Slow DAX formula to calculate # Orders Delayed with adjustment

[RegionH]

Hi

I have a datamodel with orderlines and goods receipt. I have written a formula to calculate number of unique orders delayed (in Danish # Ordre Forsinket) based on two what if parameters.

The parameter 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat] is used to adjust the delay with X number of days on those orders there are marked as indkøbsbilagsart ZCD or ZLM.

The parameter 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat] is used to adjust the delay with X number of days on those orders there are different from indkøbsbilagsart ZCD or ZLM.

The data model looks like this and there is 1.4 million rows in the table 'Ordrelinjer' and 2.775 suppliers in the table 'Leverandør':

Here is the DAX formula:

# Ordre Forsinket = 
VAR _LeveringstidRegionslagerRabat = SELECTEDVALUE( 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat], 0 )
VAR _LeveringstidOevrigeRabat = SELECTEDVALUE( 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat], 0 )

   
RETURN
CALCULATE(
    DISTINCTCOUNT('Ordrelinjer'[Indkøbsordrenummer]),
    FILTER(
        ADDCOLUMNS(
            FILTER(
                'Ordrelinjer',
                'Ordrelinjer'[Varemodtagelse] = 1 && 'Ordrelinjer'[Afvigelse i dage] > 0
            ),
            "JusteretAfvigelse", 
            'Ordrelinjer'[Afvigelse i dage] -
            IF(
                RELATED('Indkøbsbilagsart'[Indkøbsbilagsart]) IN {"ZCD", "ZLM"},
                _LeveringstidRegionslagerRabat,
                _LeveringstidOevrigeRabat
            )
        ),
        [JusteretAfvigelse] > 0
    )
)

The measure is used in a table visual, but it takes around 33 seconds to refresh the table visual. I need a few more measures in the table, so it will just take longer and longer time to refresh the table. 

Can I write the DAX formula in a more efficient, clean and performance optimized way?

Best regards 

Morten

[RegionH]

Just in case som of you run in to the same performance issue. Then I have written a more optimized code, which now only takes 216 ms to execute. I use COUNTROWS and SUMMARIZE to do the distinctcount. Here is the code:

# Ordre Forsinket Countrows + Summarize 280 ms = 

VAR _LeveringstidRegionslagerRabat = SELECTEDVALUE ( 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat] )
VAR _LeveringstidOevrigeRabat = SELECTEDVALUE ( 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat] )

   
RETURN
CALCULATE (
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                ADDCOLUMNS (
                    'Ordrelinjer',
                    "JusteretAfvigelse", 
                    'Ordrelinjer'[Afvigelse i dage] -
                        IF (
                            RELATED ( 'Indkøbsbilagsart'[Indkøbsbilagsart] ) IN {"ZCD", "ZLM"},
                            _LeveringstidRegionslagerRabat,
                            _LeveringstidOevrigeRabat
                        )
                ),
                'Ordrelinjer'[Varemodtagelse] = 1 && [JusteretAfvigelse] > 0
            ),
            'Ordrelinjer'[Indkøbsordrenummer]
        )
    )
)

[RegionH]

Thank you for pointing me in the direction of DAX Studio. I haven’t used DAX Studio to evaluate a measure and its performance before, so I had to watch a Guy in a Cube video first. The link to it is here: https://www.youtube.com/watch?v=eABg872TAJU&t=617s.

I made a small change to the code and how the distinctcount is calculated. Instead of using distinctcount, then I use SUMX and VALUES. Here is the updated code:

# Ordre Forsinket = 
VAR _LeveringstidRegionslagerRabat = SELECTEDVALUE( 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat])
VAR _LeveringstidOevrigeRabat = SELECTEDVALUE( 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat])

   
RETURN
CALCULATE(
    // DISTINCTCOUNT('Ordrelinjer'[Indkøbsordrenummer]), -- Before
    SUMX(VALUES('Ordrelinjer'[Indkøbsordrenummer]),1), -- After
    FILTER(
        ADDCOLUMNS(
            FILTER(
                'Ordrelinjer',
                'Ordrelinjer'[Varemodtagelse] = 1 && 'Ordrelinjer'[Afvigelse i dage] > 0
            ),
            "JusteretAfvigelse", 
            'Ordrelinjer'[Afvigelse i dage] -
            IF(
                RELATED('Indkøbsbilagsart'[Indkøbsbilagsart]) IN {"ZCD", "ZLM"},
                _LeveringstidRegionslagerRabat,
                _LeveringstidOevrigeRabat
            )
        ),
        [JusteretAfvigelse] > 0
    )
)

Now I see these numbers in Power BI Performance Analyzer and DAX Studio under Server Timings, which is more acceptable:

Before change:

After change:

Best regards
Morten

[lbendlin · Accepted solution]

Run your formula through DAX Studio. Enable Query Plan display. Inspect the query plan and look for excessive cartesians (high number of records). Refactor your formula and repeat.