Dax Optimization

[Akhil_1411]

Hi,

It is taking 5 mins to visual cross filter with others. I would like to know how i can optimize this dax code to perform better or anyother alternative.

Time taken:- 

The visual uses 3 different measures:

Dax_1: (USED in Visual) -

3 Months Rolling Average =
VAR A =
CALCULATE (
[Measure Selection Rolling 3 Months Avg],
FILTER (
VALUES ( 'UCD Data'[Sold_To_Ultimate_DUNS_Desc] ),
[Dynamic Segment DP DDD] <> BLANK ()
)
)
VAR B =
CALCULATE (
[Measure Selection Rolling 3 Months Avg],
FILTER (
VALUES ( 'UCD Data'[Sold_To_Ultimate_DUNS_Desc] ),
[Dynamic Segment % UCD] <> BLANK ()
)
)
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

Dax2: - 

Moving Average UCD last 3 Months =
/*Rolling three months Average*/
Var A = CALCULATE(SUM('UCD Data'[Unearned Disc]), 
DATESINPERIOD (
'Target Date1'[Date],
LASTDATE ( 'Target Date1'[Date]),
-3,
MONTH
)
)
var B = CALCULATE(SUM('UCD Data'[Total_disc]),
DATESINPERIOD (
'Target Date1'[Date],
LASTDATE ( 'Target Date1'[Date]),
-3,
MONTH
)
)
var c = (A/B)*100
return
c

DAX_3 :

Dynamic Segment % UCD =
IF(
ISFILTERED('Dynamic Segement %UCD'[Bucket]),
CALCULATE(
[unearned disc greater than 0],
FILTER(
VALUES('UCD Data'[Sold_To_Ultimate_DUNS_Desc]),
COUNTROWS(
FILTER(
'Dynamic Segement %UCD',
[_% UCD]>='Dynamic Segement %UCD'[Min]
&& [_% UCD]<'Dynamic Segement %UCD'[Max]
)
)>0
)
),
[_% UCD]
)

Thanks,

Akhil.

[Anonymous]

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.

[v-alq-msft]

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.

[Greg_Deckler]

@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.

[tex628]

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]

@tex628 ,

I have tried your logic. It is not happening. After applying the cross filtering is not happening.😐

Thanks,

Akhil.

[tex628]

What are the individual load times of these three measures? 

[Dynamic Segment DP DDD]
[Dynamic Segment % UCD]
[Measure Selection Rolling 3 Months Avg]

[Akhil_1411]

@tex628 ,

Screenshot below for individual load time.

DAX 01 and 02Dax 03

[tex628]

Hmm, for me this is a bit too hard to solve without being able to write my own code. I'm assuming the data is confidential? 

/ J

[amitchandak]

@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]
)
)

[amitchandak]

@Akhil_1411 , what is usage of VALUES ( 'UCD Data'[Sold_To_Ultimate_DUNS_Desc] )

[Akhil_1411]

@amitchandak , values taking distinct customers column and calulating the %UCD and other metrics.

This i am passing because of the other visual cross filtering

Other visual has distinct customers with % UCD. Screenshot below

[Akhil_1411]

@amitchandak ,

After modifying also it is taking the similar amount of time.