Forum Discussion

Hariharan_R's avatar
Hariharan_R
Solution Sage
2 years ago
Solved

DAX - Avoid Context transition in iterator

Hi,

I am trying to optimize a DAX measures. I have following measure (just added partial - only the virtual table section) where I could see "Context transition in iterator" problem and I was trying to avoid but not able to do.

 

VAR tab1 =
ADDCOLUMNS (
SUMMARIZE (
ALLSELECTED ( 'FT' ),
'FT'[C1_RGN],
'FT'[C2_RGN_RTLR],
'FT'[B1_VALUE],
'FT'[S1_VALUE],
'FT'[PCK],
'FT'[C_PACKS]
),
"y", ( [test])
)

 

and 

test = CALCULATE (
SUM ( 'FT'[Y_AMT] ),
ALLSELECTED (
'FT'[PCK_VALUE],
'FT'[S1_VALUE],
'FT'[S2_VALUE],
'FT'[B1_VALUE],
'FT'[S1_VALUE],
'FT'[LCL_PCK],
'FT'[C_PACKS]
)

 

Here "y" is referring another measure which has different context and scope. How to avoid Context transition in iterator in this case?

 

 

  • Try using variables to store the results before the context transition occurs.

    VAR testValue =
    CALCULATE (
    SUM ( 'FT'[Y_AMT] ),
    ALLSELECTED (
    'FT'[PCK_VALUE],
    'FT'[S1_VALUE],
    'FT'[S2_VALUE],
    'FT'[B1_VALUE],
    'FT'[S1_VALUE],
    'FT'[LCL_PCK],
    'FT'[C_PACKS] ))

    VAR tab1 =
    ADDCOLUMNS (
    SUMMARIZE (
    ALLSELECTED ( 'FT' ),
    'FT'[C1_RGN],
    'FT'[C2_RGN_RTLR],
    'FT'[B1_VALUE],
    'FT'[S1_VALUE],
    'FT'[PCK],
    'FT'[C_PACKS]),
    "y", testValue)

    If you have any questions or need more info, just hit me up!

1 Reply

  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist

    Try using variables to store the results before the context transition occurs.

    VAR testValue =
    CALCULATE (
    SUM ( 'FT'[Y_AMT] ),
    ALLSELECTED (
    'FT'[PCK_VALUE],
    'FT'[S1_VALUE],
    'FT'[S2_VALUE],
    'FT'[B1_VALUE],
    'FT'[S1_VALUE],
    'FT'[LCL_PCK],
    'FT'[C_PACKS] ))

    VAR tab1 =
    ADDCOLUMNS (
    SUMMARIZE (
    ALLSELECTED ( 'FT' ),
    'FT'[C1_RGN],
    'FT'[C2_RGN_RTLR],
    'FT'[B1_VALUE],
    'FT'[S1_VALUE],
    'FT'[PCK],
    'FT'[C_PACKS]),
    "y", testValue)

    If you have any questions or need more info, just hit me up!