optimize DAX Performance

hi, I have a DAX using CROSSJOIN, but it seems very slow; taking around 30s to show the result. Do you have any suggestion to replace that with a faster one? Count of Below DOS Target = CALCU...

Need Help

OwenAuger
7 years ago
Hi Iamnvt

Based on your description, here is a suggested change to the measure.
To offer any further suggestions, it would be useful to see at least a diagram of the data model from relationship view, and the definition of the measure [DOS Stock].

Count of Below DOS Target = VAR Target = MIN ( 'DOS Slicer'[DOS Target] ) RETURN CALCULATE ( COUNTROWS ( FILTER ( VALUES ( 'Material Master'[Mat' Alt Group] ), VAR DOSStock = [DOS Stock] RETURN DOSStock < Target && NOT ISBLANK ( DOSStock ) ) ), 'Calendar'[CurWeekOffset] = -1 )
The main changes I made were:
Store some values in variables to avoid repeated calculation: 'DOS Slicer'[Dos Target] for the entire measure, and [DOS Stock] for each iteration of FILTER.
Change CALCULATE ( DISTINCTCOUNT (... )... ), use CALCULATE ( COUNTROWS ( FILTER (...) )... ), since we are counting the distinct values of the table we are FILTER-ing anyway.
Put the 'Calendar'[CurWeekOffset] = -1 as a filter argument of CALCULATE. This seems to be a condition that needs to be applied to the entire calculation, and can't see a need to use CROSSJOIN to enforce this filter via context transition (which could have been slowing things down).

Does this measure still give the correct result, and is performance any better?

Regards,
Owen

OwenAuger

Super User

7 years ago

Iamnvt

Realised I hadn't responded to your last post!

Here are some options I can think of that might improve performance, but can't be sure.

1. Use SUMMARIZE rather than CROSSJOIN if there is a fact table (I'll call it FactTable) related to both 'Material Master' and 'Calendar:

Count of Below DOS Target (ETA) =
VAR Target =
    MIN ( 'DOS Slicer'[DOS Target] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Material Master'[Mat' Alt Group] ),
        FILTER (
            SUMMARIZE (
                FactTable,
                'Material Master'[Mat' Alt Group],
                'Calendar'[CurWeekOffset]
            ),
            VAR DOSStock = [DOS] RETURN DOSStock < Target
                && NOT ISBLANK ( DOSStock )
        )
    )

2. Use GENERATE to eliminate Mat' Alt Group values where no weeks have DOSStock below target.

Count of Below DOS Target (ETA) =
VAR Target =
    MIN ( 'DOS Slicer'[DOS Target] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Material Master'[Mat' Alt Group] ),
        GENERATE (
            VALUES ( 'Material Master'[Mat' Alt Group] ),
            FILTER (
                VALUES ( 'Calendar'[CurWeekOffset] ),
                VAR DOSStock = [DOS] RETURN DOSStock < Target
                    && NOT ISBLANK ( DOSStock )
            )
        )
    )