Forum Discussion
How do I know which DAX measure is efficient?
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] )
)
)
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
3 Replies
- Thejeswar
Super User
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
- Migasuke
Memorable Member
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 - 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
