topic Re: DAX measure to count ID's that have items in another table in DAX Commands and Tips

DAX measure to count ID's that have items in another table

serhii2000 — Sat, 23 Apr 2022 20:59:22 GMT

Dear collegues!
I need to count People_ID in 'People' table that have Item_A_ID in 'Item_A' table. The final task is to have share of People_ID in 'People' table that have Item_A_ID in 'Item_A' table.

Now, I have counted People_ID as People_count = COUNT('People'[People_ID]).

My initial solution was to create calculated column in 'People' table that calculates number of Item_A_ID from 'Item_A' table (each People_id could have 0, 1 or more such items). And then I have created measure CALCULATE(COUNT('People'[People_ID]),'People'[Number_Of_ItemA_ID]>=1). But it does not allow to filter share of People_ID by Category ID.

Could you advice me about possible solution with DAX measure?

Thank you in advance.

Re: DAX measure to count ID's that have items in another table

tamerj1 — Sat, 23 Apr 2022 21:09:37 GMT

Hi serhii2000

you can try

CALCULATE(COUNT('People'[People_ID]),'People'[Number_Of_ItemA_ID]>=1,CROSSFILTER (People[People ID], 'Items A'[People ID], BOTH)).

Re: DAX measure to count ID's that have items in another table

serhii2000 — Sun, 24 Apr 2022 06:31:15 GMT

Thank you for your reply. But unfortunately it does not work for me. I guess it is because of calculated column 'People'[Number_Of_ItemA_ID], not measure. Slicer with CategoryID cannot filter share of People_ID by Category ID. But I am not sure how to solve it.

Re: DAX measure to count ID's that have items in another table

tamerj1 — Sun, 24 Apr 2022 07:14:11 GMT

serhii2000

I was having a 2nd read of your requirement. Actually the DISTINCTCOUNT of the people ID which do exist in Items A table will give you the number you desire and will be filtered by Category.
Measure = DISTINCTCOUNT ( 'Items A'[People ID] )

Re: DAX measure to count ID's that have items in another table

serhii2000 — Sun, 24 Apr 2022 08:07:17 GMT

The task is to count People_ID in 'People' table that exist in 'Item_A' table, not just to count distinct People_ID in 'Item_A' table. And at the same time I need to have possibility to filter People_ID by Category....

In other words I need to have

#1 count of all People_ID in 'People' table

#2 count of People_ID in 'People' table that exist in 'Item_A' table in order to have share of #1

#3 create slicer that filters #2 by Category

Re: DAX measure to count ID's that have items in another table

tamerj1 — Sun, 24 Apr 2022 08:28:15 GMT

"The task is to count People_ID in 'People' table that exist in 'Item_A' table"

this is exactly the People_ID that exist in Item_A Table!
Just count them!

Re: DAX measure to count ID's that have items in another table

serhii2000 — Wed, 27 Apr 2022 11:01:55 GMT

Thank you! I did not accept your solution earlier because there was an extra active relationship in my model, that was not described in my question. I have deactivated unnecessary active relationship with wrapping it in CALCULATE with CROSSFILTER=none and your solution works well.