topic Re: Help with DAX in DAX Commands and Tips

Help with DAX

dandamudisanjay — Fri, 21 May 2021 01:39:50 GMT

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

Table 1

Re: Help with DAX

Fsciencetech — Fri, 21 May 2021 03:17:16 GMT

Hi dandamudisanjay

Hope you are good.

If you have two table, so why you are not making relationship between them with "Many to one"

you may get the related KPI's .

If it was not relavent, share few records for both table, will reply soon on that.

Re: Help with DAX

SivaMani — Fri, 21 May 2021 06:08:57 GMT

dandamudisanjay, Is your requirement to count the number of KPIs by User?

Re: Help with DAX

dandamudisanjay — Fri, 28 May 2021 02:16:33 GMT

Hi SivaMani ,

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

Re: Help with DAX

dandamudisanjay — Fri, 28 May 2021 02:16:53 GMT

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

Re: Help with DAX

SivaMani — Fri, 28 May 2021 03:31:10 GMT

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

Re: Help with DAX

dandamudisanjay — Fri, 28 May 2021 07:57:11 GMT

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?

Re: Help with DAX

SivaMani — Fri, 28 May 2021 09:11:19 GMT

dandamudisanjay,

Can you try the user column from Table 1?