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  depen...
  • samratpbi's avatar
    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!