Dax Optimization

Hi, Akhil_1411 

 

I'd like to suggest you refer to the document about Dax best practice to improve Power BI. Here are some guidance about Dax.

 

1. Clear the DAX cache before optimizing DAX

2. Format your code

3. Do not change BLANK values to zeros or other strings

4. Use the DISTINCT() and VALUES() functions consistently

5. Use ISBLANK() instead of =Blank() check

6. Use = 0 instead of checking for ISBLANK() || = 0

7. Use SELECTEDVALUE() instead of HASONEVALUE()

8. Use SELECTEDVALUE() instead of VALUES()

9. Use variables instead of repeating measures inside the IF branch

10. Use (a-b)/b along with variables instead of a/b — 1 or a/b*100–100

11. Stop using IFERROR() and ISERROR()

12. Use DIVIDE() instead of /

13. Do not use scalar variables in SUMMARIZE()

14. Use KEEPFILTERS() instead of FILTER(T)

15. Use FILTER(all(ColumnName)) instead of FILTER(values()) or FILTER(T)

16. Avoid using the AddColumns() function inside measure expressions

17. Use the correct data types based on column values

18. Use COUNTROWS instead of COUNT

19. Use SEARCH() with the last parameter

20. ALL vs. ALLExcept

 

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

It's not possible to optimize this because not only the 3 measures you've shown are involved in the calculations; they are built on top of other measures that you don't show. What's more, you don't show any data and a model on which one could carry out optimization. So, in a word, it's like you're asking somebody to tell you what's wrong with your car without giving them the car but only a description of it. Not gonna happen, I'm afraid.

Akhil_1411 ,for the first one. Try first one like

3 Months Rolling Average =
IF (
ISFILTERED ( 'DP DDD Dynamic Segmentation'[Buckets] ),
CALCULATE (
[Measure Selection Rolling 3 Months Avg],
FILTER (
VALUES ( 'UCD Data'[Sold_To_Ultimate_DUNS_Desc] ),
[Dynamic Segment DP DDD] <> BLANK ()
)
),
IF (
ISFILTERED ( 'Dynamic Segement %UCD'[Bucket] ),
CALCULATE (
[Measure Selection Rolling 3 Months Avg],
FILTER (
VALUES ( 'UCD Data'[Sold_To_Ultimate_DUNS_Desc] ),
[Dynamic Segment % UCD] <> BLANK ()
)
),
[Measure Selection Rolling 3 Months Avg]
)
)

No clue if this works, but give it a try! 🙂 

3 Months Rolling Average =

VAR Table = 
SUMMARIZE( 
VALUES ( 'UCD Data'[Sold_To_Ultimate_DUNS_Desc] ),
"Filter 1" , [Dynamic Segment DP DDD] ,
"Filter 2" , [Dynamic Segment % UCD] ,
"Rolling Avg" , [Measure Selection Rolling 3 Months Avg]
)

VAR A =
SUMX(FILTER( Table , [Filter 1] <> BLANK() , [Rolling Avg])

VAR B =
SUMX(FILTER( Table , [Filter 2] <> BLANK() , [Rolling Avg])

VAR c =
IF (
ISFILTERED ( 'DP DDD Dynamic Segmentation'[Buckets] ),
A,
IF (
ISFILTERED ( 'Dynamic Segement %UCD'[Bucket] ),
B,
[Measure Selection Rolling 3 Months Avg]
)
)
RETURN
c

Br,
J

Akhil_1411 - See my articles here on performance tuning DAX:

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813

 

Also, would greatly help to have sample data, etc. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.