Context transition in virtual tables/measures created using VAR

[Anonymous]

Hi,

I wanted to check something on virtual tables/measures created using the VAR function. I am having difficulty passing contexts in the CALCULATE function after the RETURN statement. Is there a particular way these contexts can be passed or none at all, as these are stored values. I can do the usual SUM and COUNTROWS on these tables. Even the external filters/slicers work. However, I was unable to pass a DATESINPERIOD filter for these measures/tables. I ended up creating the entire measure/table after the RETURN statement, inside the CALCULATE function.

This can become unmanageable when we have to repeat these measure/table syntaxes multiple times inside a SWITCH statement. Please help me understand if there is a way to pass the contexts to virtual tables or measures. 

Please find below the examples of what worked and what didn't:

WHAT DIDN'T:

MEASURE_INCORRECT =
VAR PGLINEARSA =
    CALCULATE (
        SUM ( PRtable[Linear] ),
        PRtable[Own] = 1,
        PRtable[ProductDescriptionF] <> "NO SEGMENT",--SA FILE
        PRtable[Analysis] = "raw_data",
        PRtable[CategoryCode] <> "DISP",
        PRtable[CategoryCode] <> "CHECK",
        PRtable[CategoryCode] <> "SOFE"
    )
VAR NONDISPLAYLINEARSA =
    CALCULATE (
        SUM ( PRtable[Linear] ),
        PRtable[ProductDescriptionF] <> "NO SEGMENT",
        --SA FILE
        PRtable[Analysis] = "raw_data",
        PRtable[CategoryCode] <> "DISP",
        PRtable[CategoryCode] <> "CHECK",
        PRtable[CategoryCode] <> "SOFE",
        ALL ( PRtable[ProductBrand] ),
        ALL ( PRtable[ProductDescriptionA] )
    )
VAR SOS = DIVIDE ( PGLINEARSA, NONDISPLAYLINEARSA ) 
VAR SELMON = IF (HASONEVALUE ( Timeframe[TIMEFRAME] ),SELECTEDVALUE ( Timeframe[TIMEFRAME] ),BLANK ())
VAR TF = IF ( HASONEVALUE ( Timeframe[TIMEFRAME] ), SELECTEDVALUE ( Timeframe[MONTHS] ), BLANK ())
VAR SELDATE = STARTOFMONTH ( '20 Time'[Day Date (Real)] )
VAR PP =  DATE ( YEAR ( SELDATE ), MONTH ( SELDATE ) - 1, 1 )
VAR CP =  DATE ( YEAR ( SELDATE ), MONTH ( SELDATE ), 1 )
VAR YA =  DATE(YEAR(SELDATE)-1,MONTH(SELDATE),1)

RETURN
SWITCH (TRUE (),

SELMON = "P1M"|| SELMON = "P3M"|| SELMON = "P6M",             
CALCULATE ( SOS, DATESINPERIOD ( '20 Time'[Day Date (Real)], SELDATE, - TF, MONTH ) ),

SELMON = "P1M PP" || SELMON = "P3M PP"|| SELMON = "P6M PP", 
CALCULATE ( SOS, DATESINPERIOD ( '20 Time'[Day Date (Real)], PP, - TF, MONTH ) ),

SELMON = "P1M YA" || SELMON = "P3M YA"|| SELMON = "P6M YA",            
CALCULATE ( SOS, DATESINPERIOD ( '20 Time'[Day Date (Real)], YA, - TF, MONTH ) ),

CALCULATE ( SOS ))

I observed that the results remained unchanged for any of the selections like P1M,P3M, P3M PP etc. The external filters worked, but not the filters in the CALCULATE after the RETURN statement.

WHAT WORKED:

MEASURE_CORRECT =
VAR SELMON =  IF (HASONEVALUE ( Timeframe[TIMEFRAME] ),SELECTEDVALUE ( Timeframe[TIMEFRAME] ), BLANK ())
VAR TF = IF ( HASONEVALUE ( Timeframe[TIMEFRAME] ), SELECTEDVALUE ( Timeframe[MONTHS] ), BLANK ())
VAR SELDATE = STARTOFMONTH ( '20 Time'[Day Date (Real)] )
VAR PP = DATE ( YEAR ( SELDATE ), MONTH ( SELDATE ) - 1, 1 )
VAR CP = DATE ( YEAR ( SELDATE ), MONTH ( SELDATE ), 1 )
VAR YA = DATE ( YEAR ( SELDATE ) - 1, MONTH ( SELDATE ), 1 )
RETURN
    SWITCH (
        TRUE (),
        SELMON = "P1M" || SELMON = "P3M" || SELMON = "P6M" || SELMON = "P12M", 
CALCULATE (
            DIVIDE (
                CALCULATE (
                    SUM ( PRtable[Linear] ),
                    PRtable[Own] = 1,
                    PRtable[ProductDescriptionF] <> "NO SEGMENT",
                    --SA FILE
                    PRtable[Analysis] = "raw_data",
                    PRtable[CategoryCode] <> "DISP",
                    PRtable[CategoryCode] <> "CHECK",
                    PRtable[CategoryCode] <> "SOFE"
                ),
                CALCULATE (
                    SUM ( PRtable[Linear] ),
                    PRtable[ProductDescriptionF] <> "NO SEGMENT",
                    --SA FILE
                    PRtable[Analysis] = "raw_data",
                    PRtable[CategoryCode] <> "DISP",
                    PRtable[CategoryCode] <> "CHECK",
                    PRtable[CategoryCode] <> "SOFE",
                    ALL ( PRtable[ProductBrand] ),
                    ALL ( PRtable[ProductDescriptionA] )
                )
            ),
            DATESINPERIOD ( '20 Time'[Day Date (Real)], SELDATE, - TF, MONTH )
        ),

        SELMON = "P1M PP" || SELMON = "P3M PP" || SELMON = "P6M PP" || SELMON = "P12M PP", 
CALCULATE (
            DIVIDE (
                CALCULATE (
                    SUM ( PRtable[Linear] ),
                    PRtable[Own] = 1,
                    PRtable[ProductDescriptionF] <> "NO SEGMENT",
                    --SA FILE
                    PRtable[Analysis] = "raw_data",
                    PRtable[CategoryCode] <> "DISP",
                    PRtable[CategoryCode] <> "CHECK",
                    PRtable[CategoryCode] <> "SOFE"
                ),
                CALCULATE (
                    SUM ( PRtable[Linear] ),
                    PRtable[ProductDescriptionF] <> "NO SEGMENT",
                    --SA FILE
                    PRtable[Analysis] = "raw_data",
                    PRtable[CategoryCode] <> "DISP",
                    PRtable[CategoryCode] <> "CHECK",
                    PRtable[CategoryCode] <> "SOFE",
                    ALL ( PRtable[ProductBrand] ),
                    ALL ( PRtable[ProductDescriptionA] )
                )
            ),
           DATESINPERIOD ( '20 Time'[Day Date (Real)], PP, - TF, MONTH )
        ),

        SELMON = "P1M YA" || SELMON = "P3M YA" || SELMON = "P6M YA" || SELMON = "P12M YA", 
CALCULATE (
            DIVIDE (
                CALCULATE (
                    SUM ( PRtable[Linear] ),
                    PRtable[Own] = 1,
                    PRtable[ProductDescriptionF] <> "NO SEGMENT",
                    --SA FILE
                    PRtable[Analysis] = "raw_data",
                    PRtable[CategoryCode] <> "DISP",
                    PRtable[CategoryCode] <> "CHECK",
                    PRtable[CategoryCode] <> "SOFE"
                ),
                CALCULATE (
                    SUM ( PRtable[Linear] ),
                    PRtable[ProductDescriptionF] <> "NO SEGMENT",
                    --SA FILE
                    PRtable[Analysis] = "raw_data",
                    PRtable[CategoryCode] <> "DISP",
                    PRtable[CategoryCode] <> "CHECK",
                    PRtable[CategoryCode] <> "SOFE",
                    ALL ( PRtable[ProductBrand] ),
                    ALL ( PRtable[ProductDescriptionA] )
                )
            ),
            DATESINPERIOD ( '20 Time'[Day Date (Real)], YA, - TF, MONTH )
        ),

        CALCULATE (
            DIVIDE (
                CALCULATE (
                    SUM ( PRtable[Linear] ),
                    PRtable[Own] = 1,
                    PRtable[ProductDescriptionF] <> "NO SEGMENT",
                    --SA FILE
                    PRtable[Analysis] = "raw_data",
                    PRtable[CategoryCode] <> "DISP",
                    PRtable[CategoryCode] <> "CHECK",
                    PRtable[CategoryCode] <> "SOFE"
                ),
                CALCULATE (
                    SUM ( PRtable[Linear] ),
                    PRtable[ProductDescriptionF] <> "NO SEGMENT",
                    --SA FILE
                    PRtable[Analysis] = "raw_data",
                    PRtable[CategoryCode] <> "DISP",
                    PRtable[CategoryCode] <> "CHECK",
                    PRtable[CategoryCode] <> "SOFE",
                    ALL ( PRtable[ProductBrand] ),
                    ALL ( PRtable[ProductDescriptionA] )
                )
            )
        )
    )

Though the above worked, you can see I ended up repeating the same  measure 4 times to cater to 4 different types of conditions. However, it does not seem to be as elegant as the one that did not work. My thinking is that the measure calculated using VAR ( SOS)  in the  first method did not seem to be changing  when we passed additional context. Is this how it is? is this the same for table variables?

Please help me in understanding if this can be achieved using the first method and probably throw some light as to why it did not work in the first method.  

[technolog · Accepted solution]

Your observations and the way you framed the problem helps in explaining the concept of context transition and the evaluation context within DAX.

Context Transition and Evaluation Context in DAX
In DAX, when you're working with measures, there are two primary contexts:

Row Context: This is present when you're iterating over rows, such as in functions like SUMX, FILTER, etc.
Filter Context: This is the set of filters applied on a table or a column, often determined by slicers, visuals, or other measures.
When you calculate a measure inside a row context, DAX automatically tries to "transition" the row context into a filter context. This is called a "context transition."

The use of VAR in a measure defines variables that are computed at the time of their definition, with the current context available at that point. Once defined, their value does not change unless explicitly re-evaluated.

In your first measure (MEASURE_INCORRECT), you're computing SOS before the RETURN statement. At this time, SOS is being evaluated once, and the result is stored in the SOS variable. Therefore, within your SWITCH statement, when you try to change the context using DATESINPERIOD, you're only altering the context for the SOS value, not for its internal calculations. As a result, the context is not passed to the original calculations of PGLINEARSA and NONDISPLAYLINEARSA.

In your second measure (MEASURE_CORRECT), you have the CALCULATE function directly wrap around the actual calculations. This means every time you apply DATESINPERIOD, you are forcing a re-evaluation of the whole expression, thus allowing the new context to influence the original calculations.

Possible Improvement
For simplification, you might want to use measures to break down the calculation parts. Here's a possible approach:

Define separate measures for the calculations:

PGLINEARSA_Measure =
CALCULATE (
SUM ( PRtable[Linear] ),
PRtable[Own] = 1,
PRtable[ProductDescriptionF] <> "NO SEGMENT",
PRtable[Analysis] = "raw_data",
PRtable[CategoryCode] <> "DISP",
PRtable[CategoryCode] <> "CHECK",
PRtable[CategoryCode] <> "SOFE"
)

NONDISPLAYLINEARSA_Measure =
CALCULATE (
SUM ( PRtable[Linear] ),
PRtable[ProductDescriptionF] <> "NO SEGMENT",
PRtable[Analysis] = "raw_data",
PRtable[CategoryCode] <> "DISP",
PRtable[CategoryCode] <> "CHECK",
PRtable[CategoryCode] <> "SOFE",
ALL ( PRtable[ProductBrand] ),
ALL ( PRtable[ProductDescriptionA] )
)
Use these measures within your main measure:
MEASURE_IMPROVED =
VAR SELMON = IF(HASONEVALUE(Timeframe[TIMEFRAME]), SELECTEDVALUE(Timeframe[TIMEFRAME]), BLANK())
VAR TF = IF(HASONEVALUE(Timeframe[TIMEFRAME]), SELECTEDVALUE(Timeframe[MONTHS]), BLANK())
VAR SELDATE = STARTOFMONTH('20 Time'[Day Date (Real)])
VAR PP = DATE(YEAR(SELDATE), MONTH(SELDATE) - 1, 1)
VAR CP = DATE(YEAR(SELDATE), MONTH(SELDATE), 1)
VAR YA = DATE(YEAR(SELDATE) - 1, MONTH(SELDATE), 1)
VAR SOS = DIVIDE([PGLINEARSA_Measure], [NONDISPLAYLINEARSA_Measure])

RETURN
SWITCH (
TRUE(),
...
...
)
By doing this, you avoid the repeated calculation syntax in your SWITCH statement, making your DAX cleaner and possibly more performant.

Remember that DAX often requires a balance between optimization for performance and clarity of code. Even though breaking calculations into smaller measures can add clarity, it can sometimes impact performance if done excessively. Always test to see what works best for your dataset and requirements.