Forum Discussion

DanielReinman's avatar
DanielReinman
Frequent Visitor
2 years ago
Solved

Switch or Filter Measures based on multiple selected values

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
 
 
  • samratpbi's avatar
    samratpbi
    2 years ago

    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!

     
     

2 Replies

  • samratpbi's avatar
    samratpbi
    Icon for Super User rankSuper User
    2 years ago

    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!