The 10 Most Common DAX Mistakes to Avoid in Power BI

Introduction

DAX (Data Analysis Expressions) is Power BI's formula language. Powerful but sometimes confusing, it's the source of many errors, even among experienced users. Here are the 10 most common mistakes we encounter at Datakhi during our audit missions.

Mistake 1: Confusing Calculated Columns and Measures

This is the most common mistake among beginners.

The Problem

Calculated columns are evaluated row by row during refresh and stored in memory. Measures are calculated on the fly according to context.

The Solution

// ERROR: Calculated column for a KPI
Total Sales = [Price] * [Quantity]

// CORRECT: Measure
Total Sales = SUMX(Sales, Sales[Price] * Sales[Quantity])

Rule: Use measures by default.

Mistake 2: Forgetting the Filter Context

Not understanding how filter context affects your measures.

Use ALL() or REMOVEFILTERS() to explicitly ignore filters:

// Share of total, works with filter contexts
Share of Total =
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)

Mistake 3: Using CALCULATE Without a Reason

CALCULATE modifies the filter context. Using it without a filter is useless.

The Problem

// USELESS: CALCULATE without filter
Total = CALCULATE(SUM(Sales[Amount]))

The Solution

// CORRECT: Without CALCULATE
Total = SUM(Sales[Amount])

// OR with an explicit filter
Total 2024 = CALCULATE(SUM(Sales[Amount]), Dates[Year] = 2024)

Mistake 4: Dividing Without Error Handling

Division by zero generates errors in your reports.

The Problem

// Potential ERROR if Cost = 0
Margin % = (Sales[Amount] - Sales[Cost]) / Sales[Cost]

The Solution

// CORRECT: Use DIVIDE
Margin % = DIVIDE(
    SUM(Sales[Amount]) - SUM(Sales[Cost]),
    SUM(Sales[Cost]),
    0  // Default value if division by 0
)

Mistake 5: Nesting CALCULATE in the Wrong Order

Nesting CALCULATEs can optimize calculation time for your measures. But the order in which you nest them can have a big impact.

The Problem

// shoes is filtered first, then france
margin shoes france =
CALCULATE(
    CALCULATE(
        [margin],
        country[name] = 'France'
    ),
    Sales[type] = 'shoes'
)

The measure will first apply the 'shoes' filter, but the cardinality of the [type] column is high, so this filter will consume a lot of calculation time.

The Solution

// France is filtered first, then shoes
margin shoes france =
CALCULATE(
    CALCULATE(
        [margin],
        Sales[type] = 'shoes'
    ),
    country[name] = 'France'
)

The country[name] column has low cardinality, so filtering will be fast. The Sales table size will have been drastically reduced, which will speed up the product type filter.

Mistake 6: Using FILTER Instead of a Direct Filter

The FILTER function returns a table, it's used for complex filters.

The Problem

// Not optimized
Sales France =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Customers, Customers[Country] = "France")
)

The Solution

// Direct filter
Sales France =
CALCULATE(
    SUM(Sales[Amount]),
    Customers[Country] = "France"
)

// Good use of FILTER
Sales profitable product =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        Products,
        [margin] > 0
    )
)

Rule: Use FILTER only when you need to filter on a measure or a complex condition.

Mistake 7: Avoid Too Deep Table Relationships

The snowflake schema is the most suitable schema for Power BI. But you need to be careful not to go to extremes.

The Problem

Beyond three dimension tables deep, the performance of DAX measures using these tables will greatly decrease.

The Solution

Don't hesitate to denormalize your tables to reduce depth.

Mistake 8: Ignoring Variables

Variables (VAR) improve both readability AND performance.

The Problem

// Calculation repeated 3 times
Measure =
IF(
    SUM(Sales[Amount]) > 10000,
    SUM(Sales[Amount]) * 0.9,
    SUM(Sales[Amount])
)

The Solution

// Calculation performed only once
Measure =
VAR TotalSales = SUM(Sales[Amount])
RETURN
IF(TotalSales > 10000, TotalSales * 0.9, TotalSales)

Mistake 9: Not Using TREATAS for Virtual Relationships

Sometimes you need to create a temporary relationship between two tables.

The Problem

Creating a physical relationship just for a one-time calculation weighs down the model.

The Solution

// Virtual relationship with TREATAS
Sales Budget =
CALCULATE(
    SUM(Budget[Amount]),
    TREATAS(VALUES(Sales[ProductID]), Budget[ProductID])
)

Mistake 10: BLANK() vs 0

BLANK() and 0 don't behave the same way in visuals.

The Problem

Returning 0 when there's no data displays unnecessary rows in your tables.

The Solution

// Returns BLANK() if no sales
Net Sales =
VAR Total = SUM(Sales[Amount]) - SUM(Returns[Amount])
RETURN IF(ISBLANK(SUM(Sales[Amount])), BLANK(), Total)

Conclusion

Avoiding these 10 mistakes will significantly improve the performance and maintainability of your Power BI reports. At Datakhi, we regularly audit Power BI models and these errors appear in 90% of cases.

Need a DAX audit of your reports? Contact our experts to optimize your measures.