Performance Problems with DAX expression

Question

I'm having a huge problem with performance in the below DAX measure.

Amount = CALCULATE (
    SUMX (
        'Financial All Scenarios',
        IF (
            SELECTEDVALUE ( 'Report Options'[Currency], "CD" ) = "USD",
            'Financial All Scenarios'[GLAmountUSD],
            IF (SELECTEDVALUE ( 'Report Options'[Currency], "CD" ) = "CD",
                'Financial All Scenarios'[GLAmountCD],
                'Financial All Scenarios'[GlAmountDoc]
            )
        )
    )
)

This DAX measure does has the following. There is a disconnected filter table called Report Options, which has 3 a field called Currency with 3 values in it CD, USD, and DOC. This slicer is used to control which field is summed up from the fact table Financial All Scenarios. If nothing is selected from the Report Options table it defaults to CD.

I have a report that has a bunch of data on it and it returns in a second when I have the Currency Report Option slicer on the report, but nothing selected. As soon as I select the CD option it is spinning forever and not returning.

How can I adjust this DAX measure so that it performs well.

Alexis Olson Alexis Olson · Accepted Answer · 2020-03-26T21:22:06

Iterative functions tend to be expensive. How about refactoring like this?

Amount =
SWITCH (
    SELECTEDVALUE ( 'Report Options'[Currency], "CD" ),
    "USD", SUM ( 'Financial All Scenarios'[GLAmountUSD] ),
    "CD", SUM ( 'Financial All Scenarios'[GLAmountCD] ),
    SUM ( 'Financial All Scenarios'[GlAmountDoc] )
)

Performance Problems with DAX expression

1 Answers