Skip to main content
cancel
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

Reply
Anonymous
Not applicable

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

Hello, 

 

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

 

1) 

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],
EncounterFact[EncounterDate.WeekStartDate]))
 
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!

 

BeforeBefore

 

AfterAfter

11 REPLIES 11
stevedep
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)

return
CALCULATE ( EncounterFact[Providers]; __Filter) 

 

Hope it helps, bit difficult without sample data. 

Kind regards, Steve.  

Anonymous
Not applicable

@stevedep 
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)

RETURN
CALCULATE ( EncounterFact[Providers], _Filter)

MatrixMatrix
 
ModalityCategoryProviderTypeServiceLineRevisedProviders MeasureSample Measure
DigitalAPPAllocated101251
DigitalAPPBariatric21254
DigitalAPPBrain & Spine151269
DigitalAPPCardiology61244
DigitalAPPFamily Medicine861414
DigitalAPPGeneral Surgery31248
DigitalAPPHematology/Oncology51233
DigitalAPPMisc41279
DigitalAPPObstetrics and Gynecology151254
DigitalAPPOrthopedic Surgery81254
DigitalAPPOther121240
DigitalAPPPain Management11254
DigitalAPPPediatrics11250
DigitalAPPRural Health71259
DigitalAPPThoracic and Vascular41212
DigitalAPPUrgent Care51123
DigitalAPPUrology71259
DigitalOtherAllocated81246
DigitalOtherBariatric1 
DigitalOtherFamily Medicine51254
DigitalOtherOrthopedic Surgery11245
DigitalOtherOther41202
DigitalOtherUrology1 
DigitalPhysicianAllocated451405
DigitalPhysicianBariatric31238
DigitalPhysicianBrain & Spine151269
DigitalPhysicianCardiology421259
DigitalPhysicianFamily Medicine1471414
DigitalPhysicianGeneral Surgery201254

Hi,

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),
ALL(ModalityCategory),
ALL(ProviderType),
ALL(ServiceLineRevised)
,EncounterFact[EncounterDate.EncounterDate] > MinDate && EncounterFact[EncounterDate.EncounterDate] < MaxDate)

RETURN
CALCULATE ( EncounterFact[Providers], _Filter)
Anonymous
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)

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

What is the calculation behind 

EncounterFact[Providers]

?

 

 

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

Anonymous
Not applicable

@stevedep

 

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 =
    CALCULATE (
        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 ) )
RETURN
    SWITCH (
        TRUE (),
        VisitTypeSelected, VisitTypeTotal,
        ChannelSelected, ChannelTotal,
        CategorySelected, CategoryTotal,
        CategoryTotal
    )

 

Hierarchy Level.PNG

Anonymous
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.

 

MatrixMatrix

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

Announcements
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