optimize DAX Performance

[Iamnvt]

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 =
CALCULATE (
    DISTINCTCOUNT ( 'Material Master'[Mat' Alt Group] ),
    FILTER (
        CROSSJOIN (
            VALUES ( 'Material Master'[Mat' Alt Group] ),
            FILTER ( VALUES ( 'Calendar'[CurWeekOffset] ), 'Calendar'[CurWeekOffset] = -1 )
        ),
        [DOS Stock] < MIN ( 'DOS Slicer'[DOS Target] )
            && NOT ISBLANK ( [DOS Stock] )
    )
)

the measure [DOS Stock] is evaluated by 2 parameters: 

'Material Master'[Mat' Alt Group]

and 

'Calendar'[CurWeekOffset]

I want to fix the [CurWeekOffset] at value = -1. That's why I used CROSSJOIN to make a table containing the 2 parameters, meanwhile still keeping the physical relationship of Calendar Table, and Master Data Table.

Any suggestions are highly appreciated. 

Sorry but I can't share the PBI file, or model. 

Thanks,

[OwenAuger · Accepted solution]

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:

1. 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.
2. Change CALCULATE ( DISTINCTCOUNT (... )... ), use CALCULATE ( COUNTROWS ( FILTER (...) )... ), since we are counting the distinct values of the table we are FILTER-ing anyway.
3. 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

[Iamnvt]

It works well, and much faster. Thank you very much, Owen.

One more question, do you know why if I used the DISTINCTCOUNT with below pattern, it didn't work out?

WIP2 = VAR Target =
    MIN ( 'DOS Slicer'[DOS Target] )
RETURN
    CALCULATE (
        DISTINCTCOUNT('Material Master'[Mat' Alt Group]), 
            FILTER (
                VALUES ( 'Material Master'[Mat' Alt Group] ),
                VAR DOSStock = [DOS] 
                RETURN DOSStock < Target
                    && NOT ISBLANK ( DOSStock )
            ),
        'Calendar'[CurWeekOffset] = -1
    )

the [CurWeekOffset] = -1 in the CALCULATE([a measure], Filter1, Filter2) didn't overwrite the filter operations in Filter1, and the end result is wrong (not filter [CurWeekOffset] = -1 at all).

[OwenAuger]

You're welcome 🙂

With the measure you just posted, I think it is giving an unexpected result due to the order that the filters are being applied (i.e. simultaneously at the moment). I think, in the context of your model, you want to filter CurWeekOffset "first", then Mat' Alt Group "second".

So I think this measure with DISTINCTCOUNT should work:

WIP2 =
VAR Target =
    MIN ( 'DOS Slicer'[DOS Target] )
RETURN
    CALCULATE (
        CALCULATE (
            DISTINCTCOUNT ( 'Material Master'[Mat' Alt Group] ),
            FILTER (
                VALUES ( 'Material Master'[Mat' Alt Group] ),
                VAR DOSStock = [DOS] RETURN DOSStock < Target
                    && NOT ISBLANK ( DOSStock )
            )
        ),
        'Calendar'[CurWeekOffset] = -1
    )

BTW I see the measure changed to [DOS] rather than [DOS Stock] - just checking is that correct?

Regards,

Owen

[Iamnvt]

Gotcha... The sequence of the filters is mattered in this case.

Thank you very much 🙂 I learned something new today.

[Iamnvt]

@OwenAuger

hi,

I have a small issue with this Measure. Instead of fixing 

'Calendar'[CurWeekOffset] = -1

, I want to count all selected slicer of [CurWeekOffset], as long as the

DOSStock < Target

in ANY [CurWeekOfset] in a ranged slicer.

[DOS] measure is dependant on MAX[CurWeekOffset].

How could I do it? I tried [ALLSELECTED}, but its not working.

Thank you in advance.

[OwenAuger]

Hi again @Iamnvt

I think the change you want to make is to replace

'Calendar'[CurWeekOffset] = -1

with

FILTER (
    ALLSELECTED ( 'Calendar'[CurWeekOffset] ),
    [DOS] < Target
)

This would filter all CurWeekOffset values seleced by slicer, keeping only those where the [DOS] measure < target.

Regards,

Owen

[Iamnvt]

@OwenAuger

hi,

The closest measure I can write to deliver the correct result is:

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

The ALLSELECTED is not working because the [DOS] measure is dependant on MAX('Calendar'[CurWeekOffset]), so it always gives the result of [DOS] with the MAX of [CurWeekOffset] in the slicer.

Meanwhile, I want to do the COUNT measure for each [curWeekOffset] in the slicer, and then deliver the distinct values of 'Material Master'[Mat' Alt Group]

Do you have other ideas to address this with COUNTROWS? or optimize the measure above?

Thank you in advance.

[OwenAuger]

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

[Iamnvt]

@OwenAuger

as I checked the query plan performance,

the SUMMARIZE is the best one, then CROSSJOIN, then GENERATE.

Thanks very much for your solution.