Forum Discussion

Iamnvt's avatar
Iamnvt
Icon for Continued Contributor rankContinued Contributor
8 years ago
Solved

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's avatar
    OwenAuger
    8 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:

    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

     

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
8 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:

  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's avatar
Iamnvt
Icon for Continued Contributor rankContinued Contributor
8 years ago

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's avatar
    OwenAuger
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      Iamnvt
      Icon for Continued Contributor rankContinued Contributor
      8 years ago

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

       

      Thank you very much :) I learned something new today.

      • Iamnvt's avatar
        Iamnvt
        Icon for Continued Contributor rankContinued Contributor
        8 years ago

        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.