Forum Discussion
Solved
Count values that are in two categories
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...
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
Super User
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] = 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 |