Multiple row Context within Iterators

[ARU_]

Hello Community,

I am trying to compute a table using DAX with date wise sales and its running average of last 5 days. 

I have this following code which works fine. However i reached here after some trial and error and i am unsure why the other DAX which i tried didn't work when i thought they should work (based on my limited knowledge and experience)

DAX - Version 1 - Works Fine

running_avg_table =


VAR date_sale_table =
    SELECTCOLUMNS (
        SUMMARIZE ( Sales, Sales[OrderDateKey], 'Date'[Date] ),
        "Date", 'Date'[Date],
        "sales", [Sales Amount]
    )
VAR final_table =
    ADDCOLUMNS (
        date_sale_table,
        "running_avg",
            AVERAGEX (
                FILTER (
                    date_sale_table,
                    [Date]
                        >= ( EARLIER ( [Date] ) - 4 )
                        && [Date] <= EARLIER ( [Date] )
                ),
                [sales]
            )
    ) 
RETURN
    final_table 

Desirable output - 

DAX Version 2 - Unable to understand why this version do not work.

In this version, I have brought AverageX function in a seperate variable and used Sumx as an outer function just to have multiple iteration of date_sale_table.

running_avg_table_1 =
VAR date_sale_table =
    SELECTCOLUMNS (
        SUMMARIZE ( Sales, Sales[OrderDateKey], 'Date'[Date] ),
        "Date", 'Date'[Date],
        "sales", [Sales Amount]
    )
VAR avg_last_5_days_sales =
    SUMX (
        date_sale_table,
        AVERAGEX (
            FILTER (
                date_sale_table,
                [Date]
                    >= ( EARLIER ( [Date], 1 ) - 4 )
                    && [Date] <= EARLIER ( [Date], 1 )
            ),
            [sales]
        )
    ) 
VAR final_table =
    ADDCOLUMNS ( date_sale_table, "running_avg", avg_last_5_days_sales ) -- invokes context transition
RETURN
    final_table 

Output from DAX 2 

I have two queries with respect to this piece of code.

1. I think there are 3 row context being created when defining "avg_last_5_days_sales" measure. First by the outer function Sumx, then by Averagex and lastly by Filter function. However, i think the DAX recognises this to have 2 row context only. This is evident from the fact that i cannot set "2" as an integer value for the 2nd parameter of Earlier function. I am not sure where i am going wrong with this?

2. Why variable "avg_last_5_days_sales" when used as a measure call in "final_table" variable do not yield the average of sales for the preceding 5 days period?

My argument here is that as this is a measure call, it should have "calculate" as the implicit outer function.

Addcolumn being an iterator should result into context transition in that case and should provide the average of the last 5 days sum. When i use Averagex function directly in the final_Table (as in case of correct DAX version), i am getting the correct answer.

Supporting Material / Info - 

One can refer to the PBIX in the following drive link

https://drive.google.com/file/d/1zwmulRDtPdA766Q6ixruYKDVFR6jddu-/view?usp=share_link

From Table relationship perspective, sales is a fact table, while date is a dimension / date table. 

Thank you everyone for your time investment in attempting to help me

Cheers

ARU 

[tamerj1]

Hi @ARU_ 
Simply becuase variables are computed only once. In this paticular case it is not about context transition, rather it is about row context generated by ADDCOLUMNS which won't be able to apply itself over a variable computed outside its iteration.

[ARU_]

Thanks @tamerj1 for patiently going through my question and helping me out here.

I forgot about the evaluation of variable to be only once.

I tried different way of writing the function. However, i am still not sure why this modified code isn't working. I removed the variable that was being evaluated once and instead put that in the iterator, so that it could be evaluated for each row. 

running_avg_table_1 =
VAR date_sale_table =
    SELECTCOLUMNS (
        SUMMARIZE ( Sales, Sales[OrderDateKey], 'Date'[Date] ),
        "Date", 'Date'[Date],
        "sales", [Sales Amount]
    )
VAR final_table =
    ADDCOLUMNS (
        date_sale_table,
        "running_avg",
            VAR avg_sales =
                CALCULATE (
                    SUMX (
                        date_sale_table,
                        AVERAGEX (
                            FILTER (
                                date_sale_table,
                                [Date]
                                    >= ( EARLIER ( [Date], 1 ) - 4 )
                                    && [Date] <= EARLIER ( [Date], 1 )
                            ),
                            [sales]
                        )
                    )
                )
            RETURN
                avg_sales
    ) -- invokes context transition
RETURN
    final_table

Secondly, i am not sure how many row context there are in defining final_table variable? I somehow not able to comprehend the concept of row context. I think that the as many iterators we have in a loop of functions, that many row context will be created. However that don't seem to be the case here. 

Many Thanks 

Regards
ARU

[tamerj1]

@ARU_ 

Again the table variable is evaluated once therefore CALCULATE cannot force context transition. 

[ARU_]

Thanks @tamerj1  for your prompt response. 

Sorry, i am still confused about it. Even if table variable is evaluated once, how will it change the working of 'Addcolumns' function. Suppose, the variable 'date_sale_table' generates a table in-memory. 

Now, in 'Addcolumns' function, we are just iterating over  'date_sale_table' variable. We are nowhere evaluating it again.. Will iterating through it also amount to evaluation?  

I hope, i am able to articulate my question. 

[tamerj1]

@ARU_ 

To explain further 

the inner AVERAGEX evalutes the average sales for each row of the table variable which is fine. Howeverfor each single iteration of ADDCOLUMNS, the outer SUMX sums the averages of all the dates in the table variable which produces the total sales over ech single row of the created table. 

[ARU_]

ok @tamerj1 . Many thanks.  appreciate your patience.

I get your point. Everything you have said makes sense to me now. However, i am still not able to accept the fact that the Calculate function doesnt invoke context transition. 

I know you have said that table variable is evaluated once and hence context transition cannot be invoked. But still i don't understand why? Why a table variable which is already evaluated treated differently from other table objects.

I tried learning more about table variables, but unable to find any suitable explanation of its relationship with Calculate in the context of transition.

[tamerj1 · Accepted solution]

@ARU_ 
I hope the following calrifies everything

[ARU_]

Thanks @tamerj1  -

You are a Star 🙂 Your approach of explanation is just awesome. I thank you for that.

After carefully study of your comments and computations, I think it is safe to say that Calculate context transition is ineffective on variable tables. 

As an extension of the above point, i want to ask when you say that newly created filter context cannot recalculate variable table during Sumx iteration, doesn't the same logic apply to AverageX iteration?

See, my understanding of context transition is application of filters from the row being iterated to the calculate expression. 

As during AverageX iteration too, you are filtering the variable table and computing Average. Doesn't this amount to recalculation of variable tables?

[tamerj1]

@ARU_ 

"when you say that newly created filter context cannot recalculate variable table during Sumx iteration, doesn't the same logic apply to AverageX iteration?" Yes

[tamerj1]

@ARU_ 

Exactly. Evaluating the complete table not only the subset of rows that belongs to the current rows iterated by ADDCOLUMNS (which then converted by CALCULATE into a filter context). 

and yes you are creating multiple row contexts created by nesting multiple iterators.