Monthly Archives: December 2020

This post purpose is to show two common mistakes that happen when using DAX variables.

Variables in DAX are a powerful tool that improves the readability of DAX code. But their name is misleading, since DAX variables behave like constants.

The syntax to define a DAX variable is straightforward:

VAR myVariable = ... a DAX expression ...
RETURN ... a DAX expression using myVariable ...

For instance, this is valid DAX code, where we define the variable TotalSales and later we use it

[% Sales] = 
VAR TotalSales = CALCULATE( [Sales], REMOVEFILTERS( 'Date' ) )
RETURN DIVIDE( [Sales Amount[, TotalSales )

Once a variable has been declared, it cannot be changed like in a conventional programming language, where variables can be assigned multiple times.

To see what are the two common mistakes that newbies do when using variables, we create two tables in Power BI Desktop using this DAX code

Date = 
ADDCOLUMNS(
    CALENDAR( "2020-01-01", "2020-12-31" ),
    "Month", FORMAT( [Date], "mmm" ),
    "Month Number", MONTH( [Date] )
)

Sales = 
VAR MinDate =
    MIN( 'Date'[Date] )
RETURN
    SELECTCOLUMNS(
        GENERATESERIES( 1, 10000 ),
        "Date", CONVERT( MOD( [Value], 365 ) + MinDate, DATETIME ),
        "Amount", MOD( [Value], 100 )
    )

Then we create a one to many relationship between ‘Date'[Date] and Sales[Date] and we set the sort by column of ‘Date'[Month] to ‘Date'[Month Number].

This is our model:

We can create the measure [Sales Amount]

Sales Amount = SUM( Sales[Amount] )

And a matrix visual to show the [Sales Amount] per Month

Now we want to write a measure to compute the percentage of the sales over the total, and to do so we write this DAX code

% Sales Amount Wrong = 
VAR SalesAmount = [Sales Amount]
VAR TotalSalesAmount =
    CALCULATE( SalesAmount, REMOVEFILTERS( 'Date' ) )
RETURN
    DIVIDE( SalesAmount, TotalSalesAmount )

But when we add this measure to the matrix visual we get this result

We have 100% per every month, which is wrong. Our mistake was to assume that the SalesAmount variable would be evaluated inside the CALCULATE with the modified filter context.

The fact is that once SalesAmount is defined, it contains the value for the current row, and when it is referred later it behaves like a constant. For instance, in January SalesAmount assumes the value of 42690, therefore the following CALCULATE behaves like if it was written

VAR TotalSalesAmount =
    CALCULATE( 42690, REMOVEFILTERS( 'Date' ) )

The correct code for this measure uses the [Sales Amount] measure instead

% Sales Amount = 
VAR SalesAmount = [Sales Amount]
VAR TotalSalesAmount =
    CALCULATE( [Sales Amount], REMOVEFILTERS( 'Date' ) )
RETURN
    DIVIDE( SalesAmount, TotalSalesAmount )

And now the result is correct

The second mistake is less evident, since it happens when we define a variable containing a table.

We want to add a card visual to show the number of Sundays of January. We think it is a good idea (but it isn’t) to first prepare a table with just the Sundays and later to count its rows with a filter on January. So we write this code

# Sundays in January Wrong = 
VAR SundaysTable =
    FILTER( ALL( 'Date' ), WEEKDAY( 'Date'[Date] ) = 1 )
RETURN
    CALCULATE( COUNTROWS( SundaysTable ), 'Date'[Month Number] = 1 )

And we find out that January has more Sundays that days

This is because the SundayTable in CALCULATE is a constant, like if it was written with literals: { { “2020-01-05”, 1, “Jan” }, { “2020-01-12”, 1, “Jan” }, … { “2020-12-27”, 12, “Dec” } }, therefore it is not affected by the filter context modified by CALCULATE.

But since the columns in table variables keep the data lineage, table variables can be used as filter arguments and the previous measure can be fixed like follows

# Sundays in January = 
VAR SundaysTable =
    FILTER( ALL( 'Date' ), WEEKDAY( 'Date'[Date] ) = 1 )
RETURN
    CALCULATE( COUNTROWS( 'Date' ), 'Date'[Month Number] = 1, SundaysTable )

And now we see the correct number of 2020 January Sundays.

Variables are very useful and once we learn to avoid these mistakes we’ll use them to better organize our code, as a support for debugging and also, in some cases, as a tool to optimize our measure performances.

The DAX code in this post was written with the purpose to be as simple as possible to show the common mistakes.

The pbix file can be downloaded from my github profile