Forum Discussion

Doerka's avatar
Doerka
Regular Visitor
4 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    Doerka
    Regular Visitor
    4 years ago

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