Forum Discussion

smoortema's avatar
smoortema
Frequent Visitor
2 years ago
Solved

Context transition for INDEX() function

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]))
            )
        )
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    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

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    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

  • smoortema's avatar
    smoortema
    Frequent Visitor
    2 years ago

    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.

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      2 years ago

      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.