Forum Discussion
Community ChampionDAX Head Scratcher
Most Valuable ProfessionalWhat's wrong with the following model?
If you use this model (removing all the bidirectional filters) and you filter/slice by using only the dimensions (Plans[PlanID], AreaOptionMaster[OptionID], and QuotesContracts[OpportunityID if you need]) you get super-fast results from both fact tables - without using any DAX measure, just using simple implicit measures.
But maybe I'm missing something.
The DAX code you wrote differs for the different evaluations of the filter in the measure because of one additional bidirectional filter. It is expected, my point is that I don't understand why the code is written the way it is written and the model is designed the way it is designed.
Community Championmarcorusso For the simplified model, this likely works just fine, but the model isn't anywhere near this simple actually. Technically, there is no actual Plans table as shown in the model that directly links to OptionMaster. It gets complex because plans actually have and show up at two additional distinctions, we will call them area and "attribute". So the Plans table currently actually has a plan ID show up multiple times in the table for each area and for each "attribute" within that area. Again, I am sure that data model improvements and optimizations can help resolve this issue. Perhaps adding a table where the Plans table is truly unique. Althought, if this was done, it would result in duplicate paths between this new Plans dimension table and OptionSelection table, one through QuotesContracts and one through OptionMaster -> AreaOptionMaster.
However, in many ways all of that is beside the point. There is nothing that explains the difference in performance of the two measures operating against the same data model, which has the bi-directional filter:
- 1Frequency = Ues straight filter clause in CALCULATE (no FILTER function) 100 times slower
- 1Frequency1 = Uses FILTER in filter clause of CALCULATE is 100 times faster than 1Frequency
Both of these functions are in the model where the bi-directional cross-filter direction exists. Both result in the exact same DAX query. One is 100 times slower than the other. So, the question is why? What is the Performance Analyzer not telling us about what is really happening? Is the DAX query shown in the Performance Analyzer just the DAX query against the model engine but does not cover what is going on in the formula engine? If that is the case, what exactly is the difference about what is going on in the formula engine between those two calculations? There is an aspect of this of solving the problem of getting a fast calculation, sure. But, the question I really want to know about is what was just stated. Why the difference in speeds? What is CALCULATE without the FILTER doing that is different than CALCULATE with the FILTER? Seems like there is some fundamental difference here but it is not exposed in the DAX queries because those are identical. It's OK if the anwer is that it is just a black box and there is no way of knowing or "well, nobody can really explain or understand how CALCULATE works, kind of like a microwave oven or non-dairy creamer". It's just one of those things I am curious to understand in terms of increasing my understanding of DAX.
- Jos_Woolley
Solution Sage
But the two expressions are not identical.
CALCULATE ( SUM ( OptionSelection[Quantity] ), QuotesContracts[PlanID] = __PlanID )is equivalent to:
CALCULATE ( SUM ( OptionSelection[Quantity] ), FILTER ( ALL ( QuotesContracts[PlanID] ), QuotesContracts[PlanID] = __PlanID ) )which differs from
CALCULATE ( SUM ( '1OptionSelection'[Quantity] ), FILTER ( '1QuotesContracts', [PlanID] = __PlanID ) )in its inclusion of the ALL statement.
As in the example I gave in my previous post, it seems that, when employed in conjunction with cross-filtering from a related table, the evaluated tables resulting from the two seemingly similar expressions may not be of the same dimension (cf my previous example, in which the 'straight' CALCULATE version - that which incorporates an implicit ALL function - evaluates to a table comprising significantly more rows than the non-ALL, FILTER version).
Regards
- marcorusso
Greg_Deckler The answer provided by Jos_Woolley is the same one I would have wrote.
In one case this filter is executed only once for every cell of the result that have the same result for the __PlanID variable (which is a cartesian product between columns that have millions of combinations):
FILTER ( ALL ( QuotesContracts[PlanID] ), QuotesContracts[PlanID] = __PlanID )The second filter must evaluate the first argument for each of the millions of combinations I mentioned before, because the filter context is potentially different in each cell:
FILTER ( '1QuotesContracts', [PlanID] = __PlanID )The query you see in Performance Analyzer is identical, but you should see a different query plan in DAX Studio.
- Greg_Deckler
marcorusso Jos_Woolley I greatly appreciate the assistance here in understanding what is going on. I hate to ask for one additional favor, I am having trouble in DAX studio simulating my table visualization in order to get the Query plan. I thought that this would do the trick:
EVALUATE ADDCOLUMNS('1OptionMaster',"__PlanID",MAX('1Plans'[PlanID]),"__Calc",[1Frequency1])However, I am not getting the same results in DAX Studio that I get in my table visualization. This is likely me just not understanding how to do what I want in DAX Studio. I'd appreciate any guidance I could get on how to emulate the table visualization in DAX Studio so that I can see what is really going on behind the scenes with this.
- Greg_Deckler
Jos_Woolley OK, but where is that captured in the actual DAX query from Performance Analyzer? In both cases the query is the following and I don't see an ALL anywhere:
// DAX Query DEFINE VAR __DS0Core = SUMMARIZECOLUMNS( ROLLUPADDISSUBTOTAL( ROLLUPGROUP('1OptionMaster'[OptionID], '1OptionMaster'[PlanID], '1Plans'[PlanID]), "IsGrandTotalRowTotal" ), "v1Frequency", '1OptionMaster'[1Frequency] ) VAR __DS0PrimaryWindowed = TOPN( 502, __DS0Core, [IsGrandTotalRowTotal], 0, '1OptionMaster'[OptionID], 1, '1OptionMaster'[PlanID], 1, '1Plans'[PlanID], 1 ) EVALUATE __DS0PrimaryWindowed ORDER BY [IsGrandTotalRowTotal] DESC, '1OptionMaster'[OptionID], '1OptionMaster'[PlanID], '1Plans'[PlanID]- Jos_Woolley
