Count values that are in two categories

[Doerka]

Hi,

I have a table with 3 columns: an ID, an User ID and a category. I am trying to get the number of users, that have both categories.

For instance:

ID	ID_user	category
1	1	A
2	2	A
3	3	B
4	1	B
5	2	C
6	3	D
7	4	A

I want the measure to return 1 when it counts the Users that have the category A and B (in this example only user 1 fullfils that condidtion).

So im looking for somtehing like:

Measure = Calculate(
   DistinctCount('table'[ID_User]),
   'table'[Category] = "A" && 'table'[Category] = "B"
)

but this obviusly does not work, and I can't find a solution.

I hope I could provide enough information, if not please let me know.  Your help would be very appreciated!

Best regards 

Doerka

[v-stephen-msft · Accepted solution]

Hi @Doerka ,

Here's my solution.

Create the first measure to get the count of category which is A or B and it is grouped by [ID_user.]

CountCategory = CALCULATE(DISTINCTCOUNT('Table'[category]),FILTER(ALLSELECTED('Table'),[ID_user ]=MAX('Table'[ID_user ])&&([category]="A"||[category]="B")))

Create the second measure to get the count of ID_user.

CountID_user = CALCULATE(DISTINCTCOUNT('Table'[ID_user ]),FILTER('Table',[CountCategory]=2))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Doerka]

It takes a bit of time to calculate, but it provides the correct number.

Thank you very much for taking your time and helping out @v-stephen-msft!

[ryan_mayu]

@Doerka 

you can try this

Measure = 
VAR tbl=SUMMARIZE('Table','Table'[ID_user],"count",CALCULATE( countrows('Table'),FILTER('Table','Table'[category]="A" ||'Table'[category]="B")))
return COUNTROWS(FILTER(tbl,[count]=2))

pls see the attachment below

[Doerka]

@ryan_mayu thank you for your quick reply.
Unfortunately does not work for me yet, for I didn't properly represent my Data in the example.

The issue is, that [ID_user] will have the same category assigned to it multiple times, like this:

ID	ID_user	category
1	1	A
2	2	A
3	3	B
4	1	B
5	2	C
6	3	D
7	1	A
8	2	A
9	1	A

The expected result for A and B should still be 1 (user 1).

[ryan_mayu]

@Doerka 

pls try this

Measure = 
VAR tbl=SUMMARIZE('Table','Table'[ID_user ],'Table'[ category])
var tbl2=SUMMARIZE(tbl,'Table'[ID_user ],"count",CALCULATE( countrows('Table'),FILTER('Table','Table'[ category]="A" ||'Table'[ category]="B")))
return COUNTROWS(FILTER(tbl2,[count]=2))

[Doerka]

Hi @ryan_mayu ,
the measure you suggested only counts the ID_user that appear [count]=2 times, wich happens in the provided Data once for [ID_user] = 2

ID	ID_user	category
2	2	A
8	2	A

If I understand your code corrrectly, you tried to SUMMARIZE instances, where ID_user and category are the same, but it seems like this does not work.

You can check for yourself by changing the last row of the data to

ID	ID_user	category
9	2	A