Forum Discussion
Using ALLEXCEPT and/or ALLSELECTED to ignore multiple active slicers except for 2 slicers/columns
Memorable MemberI 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.
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)RETURNCALCULATE ( EncounterFact[Providers], _Filter)
MatrixModalityCategory 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)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.