Forum Discussion
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 =
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,
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
9 Replies
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
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).
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
