CODEWITHSUNDEEP
powerbidax
Bill Thomas
Bill Thomas

Reputation: 13

Power BI - How do I count the number of times a value appears in relation to a separate column?

I'm trying to produce a report in Power BI to count the number of groups an individual belongs to. I have a table with users, security groups they belong to, etc.

I'm looking to query the data to help track down users that don't belong to at least two security groups. In my environment every user should be in an All Staff security group plus any other unique group they belong to.

The image I've included has exactly what I'm looking for. I currently don't have that Total column. This is what I need help with. I have the Users and all of the groups they belong to.

Here's a picture of what I'm trying to achieve:

enter image description here

I've tried the Countrows command with a filter but I couldn't get it to filter based on the number of groups users belong to.

I can't simply count the number of times a user's name appears either because this table is a lot bigger than just my example above. This is just an extra feature I'm hoping to achieve out of my table - using the analytics of my report to help find group membership issues and resolve them.

Upvotes: 1

Views: 46451

Answers (1)

RADO
RADO

Reputation: 8148

To create the calculated column, you can use this code:

Totals = CALCULATE( COUNT(TableName[Group]), ALLEXCEPT(TableName, TableName[User]))

It groups records by 'User' and then counts number of groups per each user.

I'd recommend though to use measures instead of calculated columns. Measures are much more powerful and useful ways of reporting. To create a measure:

Totals =
   SUMX(
      VALUES(Data[User]), 
      CALCULATE(COUNT(Data[Group]))
   )

Drop this measure into a matrix or a chart against the users, and you will have the desired report.

Upvotes: 3

Related Questions