Careful! VAR in your DAX formulaes

DAX DAX Calculations
1

Hello Everyone,

I was going through one of the courses offered by Enterprise DNA, and in the process I might have found an error in a video of the course.

I sent an email regarding this and EDNA team suggested it would be better if I posted this to the forum so others can learn from this.

Below are the details for the same.

Course : Mastering DAX Calculation
Section: Advanced Techniques For Variables
Video: Using variables to clean up multiple measures

The measure Export Sales by Ship Date seems to be incorrect.

I have come up with one correct implementation, but there are few things I couldn’t figure out.

The description is available in the .pbix file.

I am attaching the .pbix file for your reference. Hope you find this helpful.

MasteringDAXCalc.pbix (397.1 KB)

4 Likes
2

Hi @sparse-coder,

Welcome to the forum!
Thanks for sharing this with the community. :+1:

3

@sparse-coder Yup, you are absolutely correct variables evaluated before CALCULATE shouldn’t be used in the first argument of CALCULATE. Because variables in DAX are actually constants.

Also if you can share what else you are not able to understand then we will be able to clear your doubts.

4

This will work:

VAR thisWontWork =
CALCULATE (
    SUM ( Sales[Total Revenue] ),
    Filtered,
    USERELATIONSHIP ( Dates[Date], Sales[Ship Date] )
)
5

Just a suggestion to the EDNA team is to put some kind correction note highlight with the video stating the time within the video where the error has occurred so people know before viewing the video course. This will give the team a chance to correct the video. :slight_smile:

thanks Keith

6

Will check this out. Thanks

7

Thanks @AntrikshSharma I understand that. I was wondering why this piece of code doesn’t work.

VAR thisWontWork = CALCULATE( SUM(Sales[Total Revenue]), Filtered)

Even though the Filtered is expanded version of sales using Date—>ShipDate relation or Am I missing something.

VAR Filtered =  CALCULATETABLE( Sales
            , USERELATIONSHIP( Dates[Date], Sales[Ship Date] )
            , Sales[Channel] = "Export"
        )
8

Table expansion happened with Ship Date, but the outer CALCULATE is being evaluated with Order Dates, that’s why you need to use it once again. Only when something was ordered and delivered on the same day then only your version of the code would sum the revenue.

Also, don’t use Expanded tables where column filters would work, this is a very expensive filter, on my database of just 10 million rows, calculation is taking 4-5 seconds to run.

And USERELATIONSHIP can cause performance issues with dataset of just 1 million, I saw Alberto Ferrari talking about it, just can’t remember where.

1 Like
9

Absolutely Correct @sparse-coder,

One should be very careful while using VAR in the DAX calculation.

Let me share a situation, where I actually realized the power and cautions associated with VAR.

So, I was using time-intelligence functions in VAR. :grinning: :grinning:
and let me tell you, never take such risks.

Try to use constant results and table functions in VAR.

In fact, I use VAR a lot of times for defining virtual tables, with USERELATIONSHIP(), with CROSSFILTER() kind of functions.

I would suggest newbies to spent some time with VAR & RETURN to actually know how it works… just avoid dynamic DAX calculation in VAR.

Keep Practicing & Happy DAXing :grinning:

10

VAR can be a very good resource when working with complex measures.

What I do when creating a new measure with VAR is to actually test the output of EACH of the VAR statements as I build them. I simply comment out the RETURN part of the measure, and instead RETURN each of the VAR statements separately:

image
image803Ă—203 9.06 KB

this is tested on the actual report visual I want to see the final measure on - so I can see how it behaves with each of the VAR statements.

Once I’m done, I remove the comment hashes, and I’m good to go. :slight_smile:

4 Likes
11

Me too. It’s my “go-to” quasi-debugging method.