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

[Anonymous]

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!

Before

After

[stevedep]

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]

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

Matrix

ModalityCategory	ProviderType	ServiceLineRevised	Providers Measure	Sample Measure
Digital	APP	Allocated	10	1251
Digital	APP	Bariatric	2	1254
Digital	APP	Brain & Spine	15	1269
Digital	APP	Cardiology	6	1244
Digital	APP	Family Medicine	86	1414
Digital	APP	General Surgery	3	1248
Digital	APP	Hematology/Oncology	5	1233
Digital	APP	Misc	4	1279
Digital	APP	Obstetrics and Gynecology	15	1254
Digital	APP	Orthopedic Surgery	8	1254
Digital	APP	Other	12	1240
Digital	APP	Pain Management	1	1254
Digital	APP	Pediatrics	1	1250
Digital	APP	Rural Health	7	1259
Digital	APP	Thoracic and Vascular	4	1212
Digital	APP	Urgent Care	5	1123
Digital	APP	Urology	7	1259
Digital	Other	Allocated	8	1246
Digital	Other	Bariatric	1
Digital	Other	Family Medicine	5	1254
Digital	Other	Orthopedic Surgery	1	1245
Digital	Other	Other	4	1202
Digital	Other	Urology	1
Digital	Physician	Allocated	45	1405
Digital	Physician	Bariatric	3	1238
Digital	Physician	Brain & Spine	15	1269
Digital	Physician	Cardiology	42	1259
Digital	Physician	Family Medicine	147	1414
Digital	Physician	General Surgery	20	1254

[stevedep]

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]

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

[stevedep]

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)

[stevedep]

What is the calculation behind 

EncounterFact[Providers]

?

[Anonymous]

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

[stevedep]

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]

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

[Anonymous]

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

Matrix

Data Model

[stevedep]

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.