Forum Discussion

mark_endicott's avatar
mark_endicott
Super 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
Skilled 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.

  • mark_endicott's avatar
    mark_endicott
    Super User
    2 years ago

    DallasBaba - thank you for the further attempts, I too had been looking at a way to filter the Calendar in a variable with SUMMARIZE and then pass this to the SUMX, however whether I calculate the measure in a variable, or if I reference the original measure inside the iteration, it always produces an incorrect result, and I have tried many different options outside of your suggestions. Looking at the queries in DAX studio, this is because the iteration happens over the Calendar, rather than the FACT, or any other dimension in the visual. 

    Using CALCULATE, the calculation is run over the dimension from the visual, taking into account the filters from the CALENDAR, which is both what I need and an optimisation on the original SUMX with the referenced measure, which iterated over the FACT table. 

     

    Thank you for your assistance, I will proceed with CALCULATE and give up on teaching myself the NOCALCULATE method for now!