Forum Discussion
Count values that are in two categories
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.
@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).
Super Userpls 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))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] = 2ID 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 toID ID_user category 9 2 A