Forum Discussion
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
ARU_
I hope the following calrifies everything
10 Replies
- 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_tableSecondly, 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