Dax code optimization - any ideals?

[martyska_sze]

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" ) )
    )
)

[V-lianl-msft]

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/ 

[Greg_Deckler]

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