Forum Discussion
DAX Head Scratcher
Looking for some insight into why a DAX calculation coded in a particular way takes absolutely forever to return while a slightly different version returns quickly. I have the sample PBIX included below sig. The PBIX includes two versions of the same model, one that "works" and one that "doesn't" (in terms of speed of execution).
The data model is like this:
Plans 1-<>-* OptionMaster *-<>-1 AreaOptionMaster 1->-* OptionSelection *-<-1 QuotesContracts
Think of it this way, you have these widgets and each of these widgets has a blueprint (Plans). For each of these plans, you can add options. The same option might be applicable to multiple plans. AreaOptionMaster has your unique list of options (optionid). OptionSelection has the sales information for which options were purchased for different sales opportunities. The QuotesContracts includes your sales contract information, which includes which Plan was purchased.
OK, so the goal is to retrieve the frequency of purchases for a particular Option and Plan. So, we create a Table visual with OptionID and PlanID from the OptionMaster table and then we construct a measure like this:
Frequency =
VAR __PlanID = MAX('OptionMaster'[PlanID])
RETURN
CALCULATE(SUM(OptionSelection[Quantity]), QuotesContracts[PlanID] = __PlanID)
This works and is fast. However, let's say that you need something like the plan description from the Plans table in this visual. The minute you add this, the above measure suddently takes FOREVER to return if it doesn't run out of memory.
So, PBIX includes more details but the "fix" I came up with which is implemented in the duplicate data model in the PBIX with the tables prefixed by 1 is two fold:
- Change the relationship between QuotesContracts and OptionSelection to Both
- Change the measure to this:
1Frequency1 =
VAR __PlanID = MAX('1OptionMaster'[PlanID])
RETURN
CALCULATE(SUM('1OptionSelection'[Quantity]), FILTER('1QuotesContracts',[PlanID] = __PlanID))
So, for some odd reason, adding FILTER instead of using a straight filter clause makes a world of difference, which doesn't exactly make sense to me. Now, it's no secret that I am no fan of CALCULATE. However, I'm not exactly ready to throw CALCULATE under the bus for this one and was wondering if someone with a deep understanding of DAX could help me riddle out what is going on here. First, it doesn't make sense to me that adding a column from the Plans table would cause DAX to have conniption fits and then it doesn't make sense that an appendage table bi-directional relationship and adding FILTER would fix it.
I hate to bug you but marcorusso, you are the best person that I can think of that could answer this connundrum.
17 Replies
Consider what the DAX is doing if you have OptionsMaster[OptionID], OptionMaster[PlanID], and Plans[PlanID] as dimensions in your table visual.
Basically this:
SUMMARIZECOLUMNS ( 'OptionMaster'[OptionID], 'OptionMaster'[PlanID], 'Plans'[PlanID], "Frequency", [Frequency] )As pointed out in Indroducing SUMMARIZECOLUMNS, this is an optimized version of something more like this:
FILTER ( SUMMARIZE ( CROSSJOIN ( VALUES ( 'OptionsMaster'[OptionID] ), VALUES ( 'OptionsMaster'[PlanID] ), VALUES ( 'Plans'[PlansID] ) ), 'OptionsMaster'[OptionID], 'OptionsMaster'[PlanID], 'Plans'[PlansID], "Frequency", 'OptionsMaster'[Freqency] ), NOT ( ISBLANK ( [Frequency] ) ) )Since there are columns from separate tables, it's likely doing a Cartesian product with CROSSJOIN instead of greatly reducing the space with Auto-Exist, which means you are evaluating [Frequency] for 700,000 * 800 rows (the two columns from OptionMaster do get processed together). When you eliminate 'Plans'[PlansID], Auto-Exist does kick since you only have columns from the same table.
I would suggest that instead of using the dimension Plans[PlanID] in your table, you use a measure instead like SELECTEDVALUE ( Plans[PlanID] ) so that you get similar performance to what you see when that column isn't included.
As for explaining what's happing in your workaround, I'll just remind you that a Boolean CALCULATE argument removes and replaces the filter context on the column it modifies, so you've likely destroyed some filtering that is otherwise picked up traversing the bidirectional relationships. I haven't figured this out precisely though. Bidirectional filtering makes it harder to think about.
I can easily say that:
- The FILTER is faster with bi-di because it filters a table starting from the current filter context, which is affected by the filters applied on other tables (restricting the number of combinations to evaluate)
- The bi-di applied to all the other relationships have side effects, pushing a lot of calculation to the formula engine
What I cannot say is what a better solution is, because I don't know the business goal and I am not able to evaluate the correctness of the data model.
marcorusso AlexisOlson Yeah, I don't really have a great deal of control over this particular data model, I was kind of handed what I was handed and asked to make it work. Obviously data model changes can always help things out but I was curious about the DAX for one main reason. From what I have generally read, a lot of the blog articles and other guidance out there basically state "don't use FILTER with CALCULATE". One of the main reasons being performance since FILTER tends to create an extra table, yadda yadda. For example: https://stackoverflow.com/questions/50506030/dax-calculate-function-with-and-without-filter
However, in this case, FILTER has the exact opposite effect, it greatly improves the speed of the measure instead of slowing it down. This is further intruiging to me because it doesn't make sense to me as to why. In theory, they should both be doing the same thing. Maybe I am thinking about this wrong, but my understanding of using FILTER in CALCULATE is that using FILTER adds to the existing filter context. Not using FILTER in CALCULATE has two outcomes:
- If the columns (or tables) aren't in the filter context, then new filters will be added to the filter context to evaluate the expression.
- If the columns (or tables) are already in the filter context, the existing filters will be overwritten by the new filters to evaluate the CALCULATE expression.
So, in my way of thinking, within that visual, QuotesContracts[PlanID] is not in the current filter context so both versions of the measure should be doing the same thing, simply adding filter context.
I'm going to try some more experimentation around this so appreciate the thoughts and inputs!!
A filter in CALCULATE is always a table, conceptually.
Filter Arguments in CALCULATE - SQLBI
The filter context is a set of filters, where each filter is a table.
A filter can be on one, two, or more columns, it can also correspond to an entire table of the data model. For this reason, I use the name "filter" instead of "table filter", because a filter is always a table, just not necessarily the same table you have in the model.When you apply a filter to a filter context, it overrides existing filters over the same column(s), unless you use KEEPFILTERS.
That's it.
That's really nothing else, even though there are many consequences for that.
Now, why do you see a performance issue? Because the syntax you wrote using FILTER ( XYZ, ... ) requires a different evaluation of the table XYZ in each cell of the result unless you have something that makes the expression (the FILTER expression) identical across all the cells of the result. For example, FILTER ( ALL ( XYZ ), ... ) will never change the iterator regardless of the filter context of the cell where you evaluate the measure, even though you might still have depending expressions in the second argument (it depends on the expression).
I hope this clarifies why you are seeing the performance difference.
To me, it's very clear. My problem with the performance of the formula is why you have to write such a condition and what are the requirements and the assumptions we can make to rewrite the formula (and/or the model) in a way that simplifies the work requested to the formula engine.
