Forum Discussion

dandamudisanjay's avatar
dandamudisanjay
Regular Visitor
5 years ago

Help with DAX

Hi Experts,    Hope you all are doing well.    I'm hoping to get some help with the following issue.    We have two tables, table 1 and table 2 connected with 1-N relation  with fields as below...
@Dax
@dax @help
SivaMani's avatar
SivaMani
Resident Rockstar
5 years ago

dandamudisanjay,

Try 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 

dandamudisanjay's avatar
dandamudisanjay
Regular Visitor
5 years ago

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's avatar
    SivaMani
    Resident Rockstar
    5 years ago

    dandamudisanjay,

     

    Can you try the user column from Table 1?