Forum Discussion

ARU_'s avatar
ARU_
Advocate I
3 years ago
Solved

Multiple row Context within Iterators

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 aft...
"CALCULATE"
context transition
DAX
DAX ROW CONTEXT
  • tamerj1's avatar
    tamerj1
    3 years ago

    ARU_ 
    I hope the following calrifies everything

ARU_'s avatar
ARU_
Advocate I
3 years ago

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's avatar
tamerj1
Community Champion
3 years ago

ARU_ 

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

  • ARU_'s avatar
    ARU_
    Advocate I
    3 years ago

    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's avatar
      tamerj1
      Community Champion
      3 years ago

      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. 

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      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_'s avatar
        ARU_
        Advocate I
        3 years ago

        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.