Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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 be...
Jos_Woolley's avatar
Jos_Woolley
Icon for Solution Sage rankSolution Sage
5 years ago

Greg_Deckler 

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's avatar
marcorusso
Icon for Most Valuable Professional rankMost Valuable Professional
5 years ago

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    marcorusso Duh. Thanks. I'm going to write a blog article on this and give a big shout-out to DAX Studio and yourself!

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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. 

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    5 years ago

    marcorusso Greg_Deckler 

     

    For the sake of not hijacking this thread further (although I still have a feeling that my investigations relate to your issue, Greg), I have reproduced a simplified version of my model and posted it with my query here.

    I'd be extremely grateful if anyone could take a look at it - most likely I'm missing something very obvious which you experts will see immediately.

    Regards

  • marcorusso's avatar
    marcorusso
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Greg_Deckler you should execute in DAX Studio the same query you capture with the Performance Analyzer in Power BI.