Optimisation - Context transition in iterator

[mark_endicott]

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 suggestions is to see if I can move a calculation outside of in iteration by placing it in a variable. 

My orginal DAX is:

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]
    )

And I have attempted to optimise it by creating a variable for the total volume measure:

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 = 
	IF (
        ISFILTERED ( 'filter_list'[filter_name] ),
        CALCULATE (
            SUM ( 'MARKET'[TOTAL_QTY] ),
            USERELATIONSHIP ( 'filter_list'[ITEM], 'PRODUCT_ATTRIBUTES'[ITEM] )
        ),
        VAR calc =
            SUM ( 'MARKET'[TOTAL_QTY] )
        VAR blank_check =
            IF ( NOT ( ISBLANK ( calc ) ), calc )
        RETURN
            blank_check
    )

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_volume
    )

This makes the measure much faster (using DAX Studio), but the results are now incorrect. I understand this is likely to do with the context transition and the fact the final query now takes place over the 50,000 rows of the calendar, rather than the 13,000,000 rows of Market. 

However can anyone explain this to me in full, and make a suggestion for an optimision that would work?

(I need to use the whole table inside the ALL() rather than selected columns from the celendar, and in any case no benifit was observed)

[DallasBaba]

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]

@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!

[mark_endicott]

@DallasBaba - Thanks for this, your 2nd measure does not appear to be any different than my first one, perhaps you copied the wrong DAX?

Your first measure works, but I should have mentioned I was aware I could do this but am attempting to avoid CALCULATE. I have accepted this as a solution though as technically you have optimised my measure, and I dont want people to think it's not better than:

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]
    )

If you had intended to provide an optimal version with SUMX could you do so? 

[DallasBaba · Accepted solution]

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