Clarification on the filter contexts applied to a measure by both a slicer and a chart

[Ibu]

tl;dr I don't understand why `ALLEXCEPT('Date', 'Date'[Date])` seems to remove the filter set by the visual, while also not removing the ones provided from the outside of it/the slicer.

I'm building a chart showing running totals for transactions. To this end, I have:

• A 'Date' table. The "Period" slicer uses the 'Date'[Date] field of this table.
• A 'Transactions' table. The date column of this table is not tied to the Date table (this is because there are actually 2 date fields and I find it easier to manually filter transactions in the DAX measure than deal with the "inactive relationship" concept).
• A chart visual with 'Date'[Date] on the x-axis and my measure on the y-axis.

I have this mental model:

• Using the slicer automagically adds a filter on the 'Date' table anywhere 'Date' is queried. 
• In the chart visual, an additional filter on 'Date'[Date] is created based on the x-axis value. A measure executing in this context sees the 'Date' table with 2 filters (the intersection of them, probably); the one from the slicer and the one from the chart's x-axis current value.

My measure starts by filtering transactions manually based on the slicer's dates. I compute the start/end dates of transactions as follows:

VAR startDate = CALCULATE(MIN('Date'[Date]), ALLEXCEPT('Date', 'Date'[Date]))
VAR endDate = MAX('Date'[Date])

This works, but I don't fully understand why.

• I understand that calling MAX('Date'[Date]) returns the date of the last day of the current month on the chart (which is what I want).
• For startDate, calling MIN('Date'[Date]) would similarly return the first day of the current month on the chart, but because I'm computing running totals and want to use the slicer's start date instead, I must somehow clear the chart's 'Date'[Date] filter and only use the one coming from outside the visual. The above formula using ALLEXCEPT works, but it doesn't make sense to me as the documentation specifies it explicitly leaves the filter on the specified column 🤔. 

Why does ALLEXCEPT seem to work in this case?

 

[v-yinliw-msft]

Hi @Ibu ,

I think you just use the MIN and MAX function as the slicer outside.

When you select the slicer, the result change dynamically.

For example,  I create a sample table to explain this question.

Then I use the min and max date create a date table:

Date = CALENDAR(MIN('Transactions'[Date]), MAX('Transactions'[Date]))

Then new two measure :

MeasureVALLEXCEPT = CALCULATE(SUM(Transactions[Value]), ALLEXCEPT('Transactions','Transactions'[Date]))

MeasureVMAXANDMIN = CALCULATE(SUM('Transactions'[Value]), FILTER('Transactions', 'Transactions'[Date] <= MAX('Date'[Date]) && 'Transactions'[Date] >= MIN('Date'[Date])))

We change the slicer, we can see the MAXANDMIN is changed with the slicer, but the Allexcept is not.

Hope this helps you to understand the slicer and Allexcept function.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ibu]

Hi @v-yinliw-msft, thanks for your reply. I believe your example demonstrates my confusion as well.

Per the docs, ALLEXCEPT is: 

Removes all context filters in the table except filters that have been applied to the specified columns.

So in the MeasureVALLEXCEPT measure, it should leave the filter on Transactions[Date], yet it behaves like there is no filter at all.

As a side note, I'm now also confused why MeasureVMAXANDMIN works the way it does... given we are FILTERing within a CALCULATE, I expected there to be a context transition, meaning that MAX/MIN('Date'[Date]) would only see rows from 'Date' that match (by relationship) the Transaction's row date, and as a result the measure would always return the sum over all dates, yet it properly uses the slicer's dates (I added the table relationship for the exercise and it gives the same outcome).

[amitchandak]

@Ibu , if you are using min and max date, Using row context they will start and end date of month

If you need min selected date

minx(allselected(Date), Date[Date])