Forum Discussion

martyska_sze's avatar
martyska_sze
Frequent Visitor
4 years ago

Dax code optimization - any ideals?

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] )...
Need Help
V-lianl-msft's avatar
V-lianl-msft
Community Support
4 years ago

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/