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

tamerj1's avatar
tamerj1
Community Champion
3 years ago

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_'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.