Forum Discussion

mark_endicott's avatar
mark_endicott
Icon for Super User rankSuper User
2 years ago
Solved

Optimisation - Context transition in iterator

Hi,    I'm getting incorrect values after trying an optimisation, and I cant understand why, or if my DAX can actually be optimised. I'm using DAX Optimiser for the first time and one of it's sugge...
context transition
DAX
  • DallasBaba's avatar
    DallasBaba
    2 years ago

    Hi mark_endicott 

    Can you try the following :

     

    VAR CurrentInterval =

        SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )

    VAR StartDate = 

        SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )    

    VAR Grain =

        SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] ) 

    VAR Selected_Territory = 

        SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] ) 

    VAR calculate_volume = 

        CALCULATE (

            [Total volume],

            FILTER (

                ALL ( 'RLS CALENDAR' ),

                'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate

                    && 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval

                    && 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain

                    && 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory

            )

        )

     

    RETURN

        calculate_volume

     

    ===============OR===================

    VAR CurrentInterval =

        SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )

    VAR StartDate = 

        SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )    

    VAR Grain =

        SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] ) 

    VAR Selected_Territory = 

        SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] ) 

    RETURN

        SUMX (

            FILTER (

                ALL ( 'RLS CALENDAR' ),

                'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate

                    && 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval

                    && 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain

                    && 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory

            ),

            [Total volume]

        )

     

     

    The above two expressions calculate the Total volume measure outside of the iteration and place it in a variable. These should help improve performance by reducing the number of iterations required to calculate the expression.

     

    You can read Marco Russo's article on optimizing DAX expressions:

    https://www.sqlbi.com/articles/optimizing-dax-expressions-involving-multiple-measures/

     

    Please let me know if this help.

     

    Thanks

    Babatunde Dallas

DallasBaba's avatar
DallasBaba
Icon for Skilled Sharer rankSkilled Sharer
2 years ago

It is good to know the 1st expression makes a positive impact. You can use a combination of SUMMARIZE and SUMX to perform the aggregation. 

This approach will still adapt to the filter context while improving performance.

 

You can try :
VAR CurrentInterval = SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate = SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain = SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory = SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )
VAR FilteredCalendar = 

FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
)
RETURN
SUMX (
SUMMARIZE (
FilteredCalendar,
'RLS CALENDAR'[LEVEL_1_END_DATE], -- Group by the relevant columns
'RLS CALENDAR'[LEVEL_1_GRAIN],
'RLS CALENDAR'[TERRITORY_CODE]
),
CALCULATE(
SUM ( 'MARKET'[TOTAL_QTY] ),
USERELATIONSHIP ( 'filter_list'[ITEM], 'PRODUCT_ATTRIBUTES'[ITEM] )
)
)
============= OR use SUMX to iterate over the filtered table and calculate the sum of the “Total volume” column. Which can also help improve performance using CALCULATE function


VAR CurrentInterval = SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate = SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain = SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory = SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )


RETURN
SUMX (
FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
),
CALCULATE (
SUM ( 'MARKET'[TOTAL_QTY] )
)
)
============= OR
VAR CurrentInterval = SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate = SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain = SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory = SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )


RETURN
SUMX (
FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
),
CALCULATE(
SUM ( 'MARKET'[TOTAL_QTY] ),
USERELATIONSHIP ( 'filter_list'[ITEM], 'PRODUCT_ATTRIBUTES'[ITEM] )
)
)

 

I look forward to your response.