Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have this very simple table named "Sales",
And I am trying to add a calculated column that gives "Last Year Sales" and I dont know why one DAX formua works and the other one does not.
This one works:
This one does not work:
Solved! Go to Solution.
This is due to context transition (see article below). LastYearSales overrides the filter context of only the Year column, leaving in place the other column filters that result from context transition. LastYearSales2 filters the entire table for the specified condition, and then uses that table filter in CALCULATE. If you add "ALL(Sales)" to LastYearSales, it removes the filters resulting from context transition and returns the correct result:
LastYearSales =
VAR LastYear = Sales[Year] - 1
VAR LastYearSales =
CALCULATE (
SUM ( Sales[SalesAmount] ),
Sales[Year] = LastYear,
ALL ( Sales )
)
RETURN
LastYearSales
https://www.sqlbi.com/articles/understanding-context-transition-in-dax/
You can achieve the same result with SUMX:
LastYearSales SUMX =
VAR vYear = Sales[Year]
VAR vTable =
FILTER ( Sales, Sales[Year] = vYear - 1 )
VAR vResult =
SUMX ( vTable, Sales[SalesAmount] )
RETURN
vResult
Proud to be a Super User!
oh, that does make sense. Just when I thought I understood context transition, something like this happens and realize I still have a lot to learn.
Thanks so much !
This is due to context transition (see article below). LastYearSales overrides the filter context of only the Year column, leaving in place the other column filters that result from context transition. LastYearSales2 filters the entire table for the specified condition, and then uses that table filter in CALCULATE. If you add "ALL(Sales)" to LastYearSales, it removes the filters resulting from context transition and returns the correct result:
LastYearSales =
VAR LastYear = Sales[Year] - 1
VAR LastYearSales =
CALCULATE (
SUM ( Sales[SalesAmount] ),
Sales[Year] = LastYear,
ALL ( Sales )
)
RETURN
LastYearSales
https://www.sqlbi.com/articles/understanding-context-transition-in-dax/
You can achieve the same result with SUMX:
LastYearSales SUMX =
VAR vYear = Sales[Year]
VAR vTable =
FILTER ( Sales, Sales[Year] = vYear - 1 )
VAR vResult =
SUMX ( vTable, Sales[SalesAmount] )
RETURN
vResult
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |