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

10 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion

    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

      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

        ARU_ 

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