Forum Discussion
Switch or Filter Measures based on multiple selected values
- 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 + _targetThis 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!
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:
When partial selected:
Hope this helps.
If this help to resolve your problem, then please mark it as solution, Thanks!
- DanielReinman2 years agoFrequent Visitor
Great suggestion samratpbi - worked like a charm!