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

[serhii2000]

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.

[tamerj1]

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)).

[serhii2000]

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.

[tamerj1 · Accepted solution]

@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] )

[serhii2000]

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.

[serhii2000]

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

[tamerj1]

"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!