Using Slicer values in DAX calculations

When developing DAX calculations in order to create meaningful business metrics you normally want to remove or refine an existing filter in the current filter context.  For example, when calculating the sales contribution of a country to the sales across all countries in the Adventure Works sample database one could use:

SUM(FactInternetSales[SalesAmount])/

CALCULATE(SUM(FactInternetSales[SalesAmount]),ALL(DimDate[CalendarYear]))

This would yield the following result

So far so good.  However, what if we want to pick only certain years and have the expression dynamically recalculate only for the selected years?  We could add a slicer with the year values. This won’t work, though: the ALL function will force the filter context to evaluate across all years – regardless of slicer selection:

As you can see, DAX computes the contribution across the total for all years in the in-memory DimDate table.  The result incorrectly adds up to only 54.82% as the denominator is still taking into account all years, not just the selected ones.  How can we make this dynamic, to use only the selected values on the slicer? 

The solution is to take advantage of the inherent flexibility offered by calculated columns. We can create another ‘Year’ column in our data model, and then add another context to the calculation that will use ALL years but only within the boundaries defined by the user defined filters.  The nice thing about this solution is that the output only takes into account the years in the slicer (in other words, the query context), instead of either taking ALL years or just the CURRENT year.

So how to do this?

1)      Add a calculated column called "QueryContextYear" to the in-memory FactResellerSales table, using this DAX expression :

    RELATED(DimDate[CalendarYear])

2)      Replace the slicers in the spreadsheet with this column:  Instead of using the CalendarYear from DimDate in the slicer, use the new QueryContextYear from FactResellerSales

That’s it!  Now the result is dynamic to your slicer selection: the denominator will be calculated only using the selected years.

The reason why works is that the ALL function in the DAX expression removes the filters only on the DimDate[CalendarYear] column, but not in FactResellerSales[QueryContextYear]; The output of a calculation in DAX is always the result of stacking all the filtered tables.  So while FactResellerSales[QueryContextYear] is being filtered, DimDate[CalendarYear] is not; And the intersection of these filters determines the calculation output.