cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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 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

1 ACCEPTED SOLUTION
Community Support

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.

6 REPLIES 6
Community Support

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.

Regular Visitor

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!

Super User

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

Proud to be a Super User!

Regular Visitor

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 User

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

Proud to be a Super User!

Regular Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors