Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
@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.
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
@tex628 ,
I have tried your logic. It is not happening. After applying the cross filtering is not happening.😐
Thanks,
Akhil.
What are the individual load times of these three measures?
[Dynamic Segment DP DDD]
[Dynamic Segment % UCD]
[Measure Selection Rolling 3 Months Avg]
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
@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]
)
)
@Akhil_1411 , what is usage of VALUES ( 'UCD Data'[Sold_To_Ultimate_DUNS_Desc] )
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |