topic Filter context change - date slicer, measure in DAX Commands and Tips

Filter context change - date slicer, measure

Anonymous — Fri, 09 Feb 2024 15:08:43 GMT

Hello Everyone,

I have two tables [DimDate] and [DataTable]. Both are connected in 1 to many relationship based on date. Next i am building simple slicer and table:

Slicer uses columns from [DimDate] table.
In table visible under slicer only 'Date' column comes from [DimDate], other columns come from [DataTable].

Column 'TotalEvents' is calculated based on following measure:

Denominator = VAR Denominator = CALCULATE( SUM(DataTable[Event]), ALLEXCEPT(DataTable, DataTable[Date]) )

Why filter context is not passed to measure? In column TotalEvents i should get the same values as in 'Events' column but instead i am getting values for total events. Of course i should exclude DataTable[Equipment], DataTable[Type] etc. from the measure in order to get the same values between Events and TotalEvents but the issue is only with Date column - only those are not passed.

Re: Filter context change - date slicer, measure

UksiDuksiPryw — Sat, 10 Feb 2024 10:19:47 GMT

Can i add something to get an answer? Maybe i am not clear enough?

Re: Filter context change - date slicer, measure

talespin — Sun, 11 Feb 2024 15:32:15 GMT

hi UksiDuksiPryw , Anonymous

I am using Adventureworks to simulate your scenario. Please change table/column name accordingly.

Denominator =

VAR Denominator =
CALCULATE(
SUM(DataTable[Event]),
ALLEXCEPT(DataTable, DataTable[Date])
)

You have written ALLEXCEPT(DateTable, DateTable[Date]), it means remove all filter except Date from DataTable.

1. You should use Date from DimDate.

2, Remove all filters coming from table visual which are from DataTable (Equipmet, type, Subtype). Do not write ALL(Table name). Mention ALL(Column names individually).

Measure

-----------

MTotalSales =

CALCULATE([MSalesAmount], ALL(DimProductCategory[EnglishProductCategoryName]))