Forum Discussion
Help with DAX
Hi Fsciencetech
I'm looking to get total of ID's from table 1 which meets the following conditions.
- if KPI = "Target1" then count of ID's where category is "category 1"
- if KPI = "Target2" then count of ID's where category is "category 2"
- else, total count of ID's
Resident RockstarTry this in a measure,
Count of Id =
VAR __KPI =
MAX ( Table2[KPI] )
RETURN
SWITCH (
TRUE (),
__KPI = "Target1", CALCULATE ( COUNT ( Table1[Id] ), Table1[Category] = "category 1" ),
__KPI = "Target2", CALCULATE ( COUNT ( Table1[Id] ), Table1[Category] = "category 2" ),
COUNT ( Table1[Id] )
)Note: You may need to change the cross-filtering as bidirectional
Thanks SivaMani .
I have created the measure on table 2. It is showing stats as expected. Problem I'm now stuck with is, I have a stacked bar chart with User(from table 2) as Axis and ID(from table 1) as total values. I also have a filter for User from table 2 as slicer.
Whenever I select user in slicer (from table 2), the total ID comes up correctly which represents relevant KPI with link to category but the stacked bar chart is not reflecting the relevant ID's for the category and KPI. It is showing count of all ID's.
Please any suggestion on this?
- SivaMani