topic Re: Switch or Filter Measures based on multiple selected values in DAX Commands and Tips

Switch or Filter Measures based on multiple selected values

DanielReinman — Fri, 03 May 2024 19:30:18 GMT

I have two data sets of encounter goals (set1 and set2) for physician practices. A Practice table is joined to both data sets.

I need a measure to calculate the encounter goals + targets depending on which practices are selected in a multi-select slicer - note that multiple practices must be selectable, and they need to be selectable from one or both datasets.

Practices in set1 are an updated subset of what is in set2. I.E. So even though PracticeA exists in both datasets, it must only use the Provider Goals measure. So that if we were to sum everything, the total would be 50+60+70+25+10+15 = 230

Selectable items from set1 must use the [Provider Goals] measure.

Selectable items from set2 (and not in set1) must use the [Provider Targets] measure.

Below is what I have attempted, but it does not work when multiple items are selected from the Practice Slicer.

Goal/Target Switch =

var cond = SWITCH

(

SELECTEDVALUE(Practice[PracticeCode]),

"PracticeA",[Provider Goals],

"PracticeB",[Provider Goals],

"PracticeC",[Provider Goals],

[Provider Targets]

)

RETURN cond

Re: Switch or Filter Measures based on multiple selected values

samratpbi — Sun, 05 May 2024 20:13:15 GMT

Hi,

As I understand Set 2 is the superset, hence I have created a separate dimension table for Practice from Set2 in Power Query.

let
Source = Set2,
#"Removed Other Columns" = Table.SelectColumns(Source,{"Practice"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
#"Removed Duplicates"

Then I joined that with both the Set1 & Set2 tables.

After that I created the below measure:

Goal + Target =

VAR _goal = SUM(Set1[Goal])

VAR _target =

CALCULATE(

SUM(Set2[Target]),

FILTER(

Set_Dim,

NOT(Set_Dim[Practice]) IN SUMMARIZECOLUMNS(Set1[Practice])))

RETURN

_goal + _target

This is first with sum all the selected practice in _goal variable and then all the selected practice in _target variable which does not exists in Goal.

Below is the result (when all selected):

When partial selected:

Hope this helps.

If this help to resolve your problem, then please mark it as solution, Thanks!

Re: Switch or Filter Measures based on multiple selected values

DanielReinman — Wed, 08 May 2024 15:46:19 GMT

Great suggestion samratpbi - worked like a charm!