topic Re: Slow DAX formula to calculate # Orders Delayed with adjustment in DAX Commands and Tips

Slow DAX formula to calculate # Orders Delayed with adjustment

RegionH — Thu, 11 Jul 2024 13:56:11 GMT

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

Re: Slow DAX formula to calculate # Orders Delayed with adjustment

lbendlin — Thu, 11 Jul 2024 21:40:21 GMT

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.

Re: Slow DAX formula to calculate # Orders Delayed with adjustment

RegionH — Fri, 12 Jul 2024 06:54:40 GMT

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

Re: Slow DAX formula to calculate # Orders Delayed with adjustment

RegionH — Fri, 12 Jul 2024 09:07:46 GMT

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