topic Context transition for INDEX() function in DAX Commands and Tips

Context transition for INDEX() function

smoortema — Wed, 31 Jul 2024 14:43:18 GMT

I wrote the following code to have a table where I can see, for each customer the price plan code they had at the moment of their last transaction. The code seems to work well now. However, I do not understand why I need to add CALCULATETABLE() around Transactions table within the INDEX function. Without it, it seems that the INDEX gives back not the last transaction of the Customer, but the last transaction for all customers. In short, without CALCULATETABLE(), context transition does not work. Can anyone explain to me why is that? ADDCOLUMNS() supports context transition, and I already embedded MAX() function within a CALCULATE which should also make context transition work.

Why do i need to add CALCULATETABLE() around Transactions table?

ADDCOLUMNS (

VALUES(Transactions[Customer ID]),

"[@Price plan code]",

CALCULATE (

MAX ( Transactions[Price plan code] ),

INDEX(-1, CALCULATETABLE(Transactions), ORDERBY(Transactions[Date]))

)

Re: Context transition for INDEX() function

AlexisOlson — Wed, 31 Jul 2024 21:26:19 GMT

The filter argument for CALCULATE (the INDEX(...) part in your case), is evaluated before the context transition happens. In order for the Customer ID row context from ADDCOLUMNS to affect the Transactions table inside INDEX, you need to force a context transition to happen using CALCULATETABLE. (The outer CALCULATE does not suffice because Transactions is part of the filter argument, not the expression to be evaluated within that filter context.)

See this article for more detail on exactly this topic: Context Transition and Filters in CALCULATE - SQLBI

Re: Context transition for INDEX() function

smoortema — Thu, 01 Aug 2024 06:11:13 GMT

Thanks!

Is there a simpler, more efficient way to achieve the same result? I use this table for a measure that calculates the number of active customers, sliced by the price plan they were in for their last transaction in the selected period.

Re: Context transition for INDEX() function

AlexisOlson — Thu, 01 Aug 2024 15:08:32 GMT

There are plenty of other ways to do this but I'm not sure what is the most efficient for your particular model.

Here's another option to try:

FILTER ( SUMMARIZE ( Transactions, Transactions[Customer ID], Transactions[Price plan code], "@Date", MAX ( Transactions[Date] ) ), CALCULATE ( MAX ( Transactions[Date] ), ALL ( Transactions[Price plan code] ) ) = [@Date] )

This precalculatest the max date for each customer / code combination in your filter context and then filters it down to just the most recent code.