Forum Discussion

hideakisuzuki01's avatar
hideakisuzuki01
Helper II
3 years ago
Solved

Question on DAX

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:

 

LastYearSales2 =
VAR LastYear = 'Sales'[Year] -1
VAR LastYearSales = CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER('Sales','Sales'[Year] = LastYear))
RETURN LastYearSales

 

 

This one does not work:

 

LastYearSales =
VAR LastYear = 'Sales'[Year] -1
VAR LastYearSales = CALCULATE(
    SUM(Sales[SalesAmount]),
    'Sales'[Year] = LastYear)
RETURN LastYearSales
 
Even without using the FILTER function, I thought the DAX engine automatically adds the FILTER functions before it gets processed so I dont know why one works and the other does not.
 

 

 

 

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    hideakisuzuki01,

     

    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

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    hideakisuzuki01,

     

    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

     

     

  • hideakisuzuki01's avatar
    hideakisuzuki01
    Helper II
    3 years ago

    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 !