Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Not applicable

Using ALLEXCEPT and/or ALLSELECTED to ignore multiple active slicers except for 2 slicers/columns



I have a report that contains 1 table in the data model: "EncounterFact". It has 2 measures:



Providers (ALLEXCEPT 2) = CALCULATE ( CALCULATE (EncounterFact[Providers], ALLEXCEPT( EncounterFact, EncounterFact[EncounterDate.EncounterDate])),ALL())
2) Providers (ALLEXCEPT 3) = CALCULATE ( EncounterFact[Providers], DATESBETWEEN (EncounterFact[EncounterDate.EncounterDate], FIRSTDATE ( EncounterFact[EncounterDate.EncounterDate] ), LASTDATE ( EncounterFact[EncounterDate.EncounterDate] )), ALLEXCEPT (EncounterFact,EncounterFact[EncounterDate.EncounterDate],
Below is a list of visual slicers users can select from. I would like to find a formula that ignores all the slicers a user selects except for "Week of:" and "Visit Date". Also, I plan on using the "Visit Date" slicer as an active filter on the Filter pane of the report, and only change if a user overrides the filter with a different date selection on the visual slicer. So essentially I want my measure to retain the active date filter of EncounterDate.EncounterDate and ignore the other slicer visuals used.
The first image below shows the "Providers (ALLEXCEPT 3)" measures correctly shows the DISTINCTCOUNT of providers within the active date range of 1/1/20 - 6/7/20, which is 1,414. However, once I activate a combination of slicer visuals like in the second image, the value changes when it should stay as 1,414. I wonder if I need to use KEEPFILTERS or ALLSELECTED in some way that retains my active date filter on the column EncounterDate.EncounterDate to return it as a pre-filtered table and then have ALLEXCEPT apply so that it ignores any applied filters from the slicers.
Any ideas? Thanks!





Memorable Member
Memorable Member

I suggest to go into the direction of:

MeasureName = 
var __MaxDate = Max(EncounterFact[EncounterDate.EncounterDate])
var __MinDate = Min(EncounterFact[EncounterDate.EncounterDate])
var __Filter = Filter(all(EncounterFact,EncounterFact);EncounterFact[EncounterDate.EncounterDate] > __MindDate && EncounterFact[EncounterDate.EncounterDate] < __MaxDate)

CALCULATE ( EncounterFact[Providers]; __Filter) 


Hope it helps, bit difficult without sample data. 

Kind regards, Steve.  

Not applicable

Hi Steve, thanks so much for the feedback. I went ahead and tried the measure you suggested using the code below like you had, but appears the explicit/external slicers/filters are impacting the measure still and not being ignored. You mentioned not having sample data so I've pasted a sample table below of my data exported from a matrix visual. It contains the 3 slicers (ModalityCategory, ProviderType and ServiceLineRevised) that appear to be affecting the measure in question. The "Providers" column is the measure showing DISTINCTCOUNT of the lowest granularity, providerID. The "Sample Measure" is the one you suggested. Some rows show 1,414, which is correct, but others show up with various amounts and I'm not sure why, but I want them to show the 1,414, which is the total. I've also attached a photo of the matrix visual.


I appreciate your help!

Sample Measure =
VAR MaxDate = Max(EncounterFact[EncounterDate.EncounterDate])
VAR MinDate = Min(EncounterFact[EncounterDate.EncounterDate])
VAR _Filter = Filter(ALL(EncounterFact),EncounterFact[EncounterDate.EncounterDate] > MinDate && EncounterFact[EncounterDate.EncounterDate] < MaxDate)

CALCULATE ( EncounterFact[Providers], _Filter)

ModalityCategoryProviderTypeServiceLineRevisedProviders MeasureSample Measure
DigitalAPPBrain & Spine151269
DigitalAPPFamily Medicine861414
DigitalAPPGeneral Surgery31248
DigitalAPPObstetrics and Gynecology151254
DigitalAPPOrthopedic Surgery81254
DigitalAPPPain Management11254
DigitalAPPRural Health71259
DigitalAPPThoracic and Vascular41212
DigitalAPPUrgent Care51123
DigitalOtherFamily Medicine51254
DigitalOtherOrthopedic Surgery11245
DigitalPhysicianBrain & Spine151269
DigitalPhysicianFamily Medicine1471414
DigitalPhysicianGeneral Surgery201254


It depends on the data model if this will work, but you can try:

Sample Measure =
VAR MaxDate = Max(EncounterFact[EncounterDate.EncounterDate])
VAR MinDate = Min(EncounterFact[EncounterDate.EncounterDate])
VAR _Filter = Filter(ALL(EncounterFact),
,EncounterFact[EncounterDate.EncounterDate] > MinDate && EncounterFact[EncounterDate.EncounterDate] < MaxDate)

CALCULATE ( EncounterFact[Providers], _Filter)
Not applicable

@stevedep , thanks for getting back to me. I tried writing the formula, but the FILTER function will only take 1 table and a FilterExpression. The additional ALL functions for ModalityCategory, ProviderType and ServiceLineRevised can't be added and they are actually columns, not tables. I only have 1 table, EncounterFact, in my data model.

Any other ideas? Seems strange that using ALL or ALLEXCEPT doesn't return the 1,414 total number of the table when multiple external slicers/filters are applied to the measure. You'd think it would ignore them, but not if multiple are applied apparently.

My bad, probably it could be:

difficult to test without the model and detailed sample data, but lets see:

Sample Measure =
VAR MaxDate = Max(EncounterFact[EncounterDate.EncounterDate])
VAR MinDate = Min(EncounterFact[EncounterDate.EncounterDate])
VAR _Filter = Filter(ALL(EncounterFact)
,EncounterFact[EncounterDate.EncounterDate] > MinDate && EncounterFact[EncounterDate.EncounterDate] < MaxDate)

CALCULATE ( EncounterFact[Providers], ALL(ModalityCategory),
ALL(ServiceLineRevised), _Filter)

What is the calculation behind 





Not applicable

@stevedep  The single table, EncounterFact, in the data model is coming from a SSAS Tabular cube source, which is why there aren't seperate tables.


The measure:

EncounterFact[Providers] is: CALCULATE ( DISTINCTCOUNT ( 'EncounterFact'[Encounters.ProviderID] ), EncounterFact[Encounters.ProviderID] > 0 )
So the DISTINCTCOUNT of Encounters.ProviderID of the entire date range is 1,455, but between 1/1/20 - 6/7/20 it is 1,414. It seems easy to return the 1,455 of the whole table that the slicers don't impact. Somehow I need to return an expanded DAX table that is filtered with that date slicer to give the 1,414 value I want, and then use ALL or ALLEXCEPT to override the non-date slicers in my menu.

That's helpful, in that case it's probably;


Sample Measure = VAR MaxDate = Max(EncounterFact[EncounterDate.EncounterDate]) VAR MinDate = Min(EncounterFact[EncounterDate.EncounterDate]) VAR _Filter = Filter(ALL(EncounterFact) ,EncounterFact[EncounterDate.EncounterDate] > MinDate && EncounterFact[EncounterDate.EncounterDate] < MaxDate &&
EncounterFact[Encounters.ProviderID] > 0
RETURN CALCULATE ( distinctcount( EncounterFact[Encounters.ProviderID])
, _Filter)

Not applicable



Hey Steve, unfortunately that didn't work either. I actually started to go down a seperate path using some variables and SWITCH. I probably should post this as a new post on the forum, but thought you might look at it and see if it works better. Below is a screenshot of the Excel spreadsheet showing 2 examples. The first example contains my sample data and slicer visuals available to select from. In the first example you can see all 3 slicers are active with a selection. The second example shows what should happen if 1 slicer is selected. I've added some notes with the examples, but I want to find the DAX that would work for BOTH the yellow ? boxes.


Here is some of the DAX that I started for the measure. Have any ideas on this to make it work? Thanks so much again for your help.

Sample Measure = 
// Measure 1:
VAR ProviderID =
        DISTINCTCOUNT ( EncounterFact[Encounters.ProviderID] ),
        EncounterFact[Encounters.ProviderID] > 0
// Hierarchy levels in order of smallest (most granular) to largest:
VAR VisitTypeSelected =
    ISFILTERED ( EncounterFact[Encounters.VisitType] )
VAR ChannelSelected =
    ISFILTERED ( EncounterFact[ModalityRevised] )
VAR CategorySelected =
    ISFILTERED ( EncounterFact[ModalityCategory] ) 
// Denominator measures based on slicer selection:
VAR VisitTypeTotal =
    CALCULATE ( ProviderID, ALL ( EncounterFact[Encounters.VisitType] ) )
VAR ChannelTotal =
    CALCULATE ( ProviderID, ALL ( EncounterFact[ModalityRevised] ) )
VAR CategoryTotal =
    CALCULATE ( ProviderID, ALL ( EncounterFact ) )
    SWITCH (
        TRUE (),
        VisitTypeSelected, VisitTypeTotal,
        ChannelSelected, ChannelTotal,
        CategorySelected, CategoryTotal,


Hierarchy Level.PNG

Not applicable

@stevedep I tried this revised one and I get the results below in the Matrix photo. Somehow the total needs to be at the row level, but I get these various row totals. Unfortunately I can't share many details with the data since it's healthcare, but I attached a second photo showing the single table I have in the data model.


I appreciate your continued help.



Data ModelData Model

The best way to get desired result is to create dimension table and a date table and then join these with your fact table. Most issues on the forum have as root cause a sub optimal data model.

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors