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 !
How you would optimise measure below? I tried to replace FILTER on KEEPFILTERS but performance stays the same.
Final_measure =
SWITCH (
SELECTEDVALUE ( 'Table1'[Column1] ),
1,
SWITCH (
SELECTEDVALUE ( 'Table3'[Column0] ),
"Reve", CALCULATE ( [Measure0], FILTER ( 'Table2', 'Table2'[Column0] = "Text0" ) ),
"PPO", CALCULATE ( [Measure0], FILTER ( 'Table2', 'Table2'[Column0] = "Text1" ) ),
"KO",
CALCULATE ( [Measure0], FILTER ( 'Table2', 'Table2'[Column0] = "Text2" ) )
+ CALCULATE ( [Measure0], FILTER ( 'Table2', 'Table2'[Column0] = "Text3" ) )
+ CALCULATE ( [Measure0], FILTER ( 'Table2', 'Table2'[Column0] = "Text4" ) )
+ CALCULATE ( [Measure0], FILTER ( 'Table2', 'Table2'[Column0] = "Text5" ) )
+ CALCULATE ( [Measure0], FILTER ( 'Table2', 'Table2'[Column0] = "Text6" ) ),
"PKO", CALCULATE ( [Measure0], FILTER ( 'Table2', 'Table2'[Column0] = "Text7" ) ),
"EBIT", CALCULATE ( [Measure0], FILTER ( 'Table2', 'Table2'[Column0] = "Text8" ) )
),
0,
SWITCH (
SELECTEDVALUE ( 'Table3'[Column0] ),
"Reve", CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text0" ) ),
"PPO", CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text1" ) ),
"KO",
CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text2" ) )
+ CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text3" ) )
+ CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text4" ) )
+ CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text5" ) )
+ CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text6" ) ),
"PKO", CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text7" ) ),
"EBIT", CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text8" ) )
),
SWITCH (
SELECTEDVALUE ( 'Table3'[Column0] ),
"Reve", CALCULATE ( [Measure2], FILTER ( 'Table2', 'Table2'[Column0] = "Text0" ) ),
"PPO", CALCULATE ( [Measure2], FILTER ( 'Table2', 'Table2'[Column0] = "Text1" ) ),
"KO",
CALCULATE ( [Measure2], FILTER ( 'Table2', 'Table2'[Column0] = "Text2" ) )
+ CALCULATE ( [Measure2], FILTER ( 'Table2', 'Table2'[Column0] = "Text3" ) )
+ CALCULATE ( [Measure2], FILTER ( 'Table2', 'Table2'[Column0] = "Text4" ) )
+ CALCULATE ( [Measure2], FILTER ( 'Table2', 'Table2'[Column0] = "Text5" ) )
+ CALCULATE ( [Measure2], FILTER ( 'Table2', 'Table2'[Column0] = "Text6" ) ),
"PKO", CALCULATE ( [Measure2], FILTER ( 'Table2', 'Table2'[Column0] = "Text7" ) ),
"EBIT", CALCULATE ( [Measure2], FILTER ( 'Table2', 'Table2'[Column0] = "Text8" ) )
)
)
In my opinion, Final_measure =
SWITCH (
SELECTEDVALUE ( 'Table1'[Column1] ),
1,
xxxx,
0,
xxxx,
xxxx
) can be deleted, which seems to be invalid logic.
Then you can use nested if instead of switch:
IF(
SELECTEDVALUE ( 'Table3'[Column0] )="Reve",
, CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text0" ) ),
IF(
SELECTEDVALUE ( 'Table3'[Column0] )="PPO",
CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text1" ) ),
IF(
SELECTEDVALUE ( 'Table3'[Column0] )="KO",
SUMX (FILTER ( 'Table2', 'Table2'[Column0] IN{ "Text2", "Text3","Text4","Text5","Text6"}),[Measure1]),
IF(
SELECTEDVALUE ( 'Table3'[Column0] )="PKO",
CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text7" ) ),
IF(
SELECTEDVALUE ( 'Table3'[Column0] )="EBIT",
CALCULATE ( [Measure1], FILTER ( 'Table2', 'Table2'[Column0] = "Text8" ) )
)
)
)
)
)
https://www.sqlservice.se/dax-quick-performance-tip-replace-switch-with-nested-ifs/
@martyska_sze See if this series of 2 blog articles helps:
Performance Tuning DAX - Part 1 - Microsoft Power BI Community
Performance Tuning DAX - Part 2 - Microsoft Power BI Community
There is a recent comment on Part 2 that demonstrates the potential value of optimizing your data model.
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |