Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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...
ALLEXCEPT
ALLSELECTED
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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
 
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
stevedep's avatar
stevedep
Icon for Memorable Member rankMemorable Member
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      stevedep
      Icon for Memorable Member rankMemorable Member
      6 years ago

      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)
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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