How do I know which DAX measure is efficient?

[anilpoda]

If there are multiple ways to create a DAX measure which yields same result, then how would I know which one to use in my Power BI project? For example using below 2 DAX formulas we can get current year weekly sales, which one is efficient and how? What is the  role of permformace analyzer here?

CYW = 
CALCULATE(SUM(Fact_OrderLines[Value]),
FILTER(ALL('Date'),
'Date'[Year] =SELECTEDVALUE('Date'[Year])&&
'Date'[Week No] =SELECTEDVALUE('Date'[Week No])))

CYW2 = CALCULATE (
    SUM ( Fact_OrderLines[Value] ),
    FILTER (
        ALL ( Fact_OrderLines ),
        YEAR ( [Order Date] ) = MAX ( 'Date'[Year] )
            && WEEKNUM ( Fact_OrderLines[Order Date] ) = MAX ( 'Date'[Week No] )
    )
)

[Thejeswar · Accepted solution]

@anilpoda ,

At first sight, felt the CYW2 measure would be a little slow seeing the MAX() function in the query while the other one does not have any additional functions rather than the selectedvalue()

Tried that out with DAX studio to understand the performance with very little data. The Below is the output

CYW

CYW2

The Performance is very close with a difference of 3 ms. But still CYW2 is faster

[ribisht17]

Hi @anilpoda 

In-case you want to know more about the Performance Analyser

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer

Regards,

Ritesh

[Migasuke]

Hi @anilpoda ,

You have a very good question, because optimisations should be one of the priorities.
The best way how to measure performance of your DAX is via DAX Studio software. It offers more options and details than basic Perf. Analyzer (and sometimes they are actually used together)

In DAX studio you can measure how fast is proccessed, how much performance it takes etc. It is a bit complex topic due to that it is better to check some videos, for example here:

https://www.youtube.com/watch?v=C5HBhlLUFsE&t=1s