DAX switch function - Optimisation

[Nishantjain]

Hi there, 

I have been working on optimising the SWITCH function and have had good success. I was able to reduce a measure that took over 12s to just 1s. 

The problem that I have is that I am unable to reduce it further. If you look at the server timings, you can see that it is FE that is taking over 90% of the time. 

The measure is below 

Key Comparator DEP =
VAR selectedkey =
    SELECTEDVALUE ( 'Comparators Slicer'[Comparators] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Comparators Slicer'[Comparators] ),
        "Actual (Mth)", CALCULATE (
            [DEP Actual],
            'Drillable Profit Center Hierarchy'[Level 1 Code Sort Order]
                = IF ( selectedkey = "Actual (Mth)", 0, 1 )
        ),
        "Plan (Mth)", CALCULATE (
            [DEP Month Plan],
            'Drillable Profit Center Hierarchy'[Level 1 Code Sort Order]
                = IF ( selectedkey = "Plan (Mth)", 0, 1 )
        ),
        "PY (Mth)", CALCULATE (
            [DEP Month PY],
            'Drillable Profit Center Hierarchy'[Level 1 Code Sort Order]
                = IF ( selectedkey = "PY (Mth)", 0, 1 )
        ),
        "Actual vs Plan (Mth)", CALCULATE (
            [DEP Month vs Plan],
            'Drillable Profit Center Hierarchy'[Level 1 Code Sort Order]
                = IF ( selectedkey = "Actual vs Plan (Mth)", 0, 1 )
        ),
        "Actual vs PY (Mth)", CALCULATE (
            [DEP vs PY],
            'Drillable Profit Center Hierarchy'[Level 1 Code Sort Order]
                = IF ( selectedkey = "Actual vs PY (Mth)", 0, 1 )
        ),
        "Actual (YTD)", CALCULATE (
            [DEP YTD Actual],
            'Drillable Profit Center Hierarchy'[Level 1 Code Sort Order]
                = IF ( selectedkey = "Actual (YTD)", 0, 1 )
        ),
        "Plan (YTD)", CALCULATE (
            [DEP YTD Plan],
            'Drillable Profit Center Hierarchy'[Level 1 Code Sort Order]
                = IF ( selectedkey = "Plan (YTD)", 0, 1 )
        ),
        "PY (YTD)", CALCULATE (
            [DEP YTD PY],
            'Drillable Profit Center Hierarchy'[Level 1 Code Sort Order]
                = IF ( selectedkey = "PY (YTD)", 0, 1 )
        ),
        "Actual vs Plan (YTD)", CALCULATE (
            [DEP YTD vs Plan],
            'Drillable Profit Center Hierarchy'[Level 1 Code Sort Order]
                = IF ( selectedkey = "Actual vs Plan (YTD)", 0, 1 )
        ),
        "Actual vs PY (YTD)", CALCULATE (
            [DEP YTD vs PY],
            'Drillable Profit Center Hierarchy'[Level 1 Code Sort Order]
                = IF ( selectedkey = "Actual vs PY (YTD)", 0, 1 )
        ),
        "FY Plan", CALCULATE (
            [DEP FY Plan],
            'Drillable Profit Center Hierarchy'[Level 1 Code Sort Order]
                = IF ( selectedkey = "FY Plan", 0, 1 )
        ),
        BLANK ()
    )

I have tried to understand the physical query plan but no matter what I do, I can't reduce the time taken by the FE. 

Any help is much appreciated. 

Thanks

Nishant

[v-yulgu-msft · Accepted solution]

Hi @Nishantjain ,

Formula Engine (FE) is single-threaded. If the used formulas in your report is too much complex, it will reduce its performance. For how to improve the performance of above measure which wrapped multiple measures within Switch function, please refer to:

Optimizing IF and SWITCH expressions using variables

Optimizing DAX expressions involving multiple measures

Formula engine and storage engine in SSAS

Best regards,

Yuliana Gu

[Nishantjain]

@v-yulgu-msft 

I have already implemented the recommendation in the links that you have provided. I guess there isn't any more opportunity to optimise the calculation

Thanks for your response

Nishant