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.
Hello,
I have a measure in my report for counting the sum of the orderbook.
This measures must check two criteria. One for the days to count en the other te ratecode.
Below a part of the selection of this measure.
Is it possible to write this measures better for a lower CPU?
Thanks,
Ronald
OrderbookDays= SUMX(ContItems;
SWITCH(TRUE();
[CountDays]=1 && ContItems[RATECODE]="STD";ContItems[RATE#1];
[CountDays]=1 && ContItems[RATECODE]="FERR";ContItems[RATE#1];
[CountDays]=1 && ContItems[RATECODE]="STD3";ContItems[RATE#1];
[CountDays]=2 && ContItems[RATECODE]="STD";ContItems[RATE#2];
[CountDays]=2 && ContItems[RATECODE]="FERR";ContItems[RATE#2];
[CountDays]=2 && ContItems[RATECODE]="STD3";ContItems[RATE#2];
[CountDays]=3 && ContItems[RATECODE]="STD";ContItems[RATE#3];
[CountDays]=3 && ContItems[RATECODE]="FERR";ContItems[RATE#3];
[CountDays]=3 && ContItems[RATECODE]="STD3";ContItems[RATE#3];
[CountDays]=4 && ContItems[RATECODE]="STD";ContItems[RATE#4];
[CountDays]=4 && ContItems[RATECODE]="STD3";ContItems[RATE#4])
Solved! Go to Solution.
Hi @Ronald123,
I don't suggest you to use complex calculate formula as expression with sumx or other functions who contains loop features.(Obviously, it will affect performance)
For your scenario, you can try to use below measure if it work on your side with cost less system resource.
OrderbookDays = SUMX ( ContItems; IF ( ContItems[RATECODE] IN { "STD"; "STD3"; "FERR" }; SWITCH ( [CountDays]; 1; ContItems[RATE#1]; 2; ContItems[RATE#2]; 3; ContItems[RATE#3];
4; ContItems[RATE#4] ) ) )
Optimizing DAX expressions involving multiple measures
Regards,
Xiaoxin Sheng
Hi @Ronald123,
I don't suggest you to use complex calculate formula as expression with sumx or other functions who contains loop features.(Obviously, it will affect performance)
For your scenario, you can try to use below measure if it work on your side with cost less system resource.
OrderbookDays = SUMX ( ContItems; IF ( ContItems[RATECODE] IN { "STD"; "STD3"; "FERR" }; SWITCH ( [CountDays]; 1; ContItems[RATE#1]; 2; ContItems[RATE#2]; 3; ContItems[RATE#3];
4; ContItems[RATE#4] ) ) )
Optimizing DAX expressions involving multiple measures
Regards,
Xiaoxin Sheng
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 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |