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 · Accepted Answer · 2020-03-26 21:22:06Z

2

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

answered Mar 26, 2020 at 21:22

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paul Cavacas Over a year ago

Thanks that appears to work well. I will have to read a little more to understand the difference between them from a Power BI standpoint. I get what they both do, but just in how the actually work.

Alexis Olson Over a year ago

The basic aggregation functions like SUM can do a lot of optimization behind the scenes. Using SUMX forces it to instantiate a physical table and iterate through every single row and run through the IF logic every single row. Even without behind the scenes optimization, the way I wrote it you only have to do the switching logic once.

Collectives™ on Stack Overflow

Performance Problems with DAX expression

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related