Filter context change - date slicer, measure

[UksiDuksi]

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.

[talespin · Accepted solution]

hi @UksiDuksiPryw , @UksiDuksi 

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]))

[UksiDuksiPryw]

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