Define if a unique value in one column has more than value attributed to them in another column

[Anonymous]

Hi there, Post-Christmas mental block here. I have a Power BI column containing users. Each user has a Team which is in another column. There are mistakes in the raw data, where a User has been assigned to the wrong team so they will have more than one team. I can create table which shows each user and the team(s) but I want to be able to filter so I can only see the users with more than one team name. Can I create a measure that does this? (Or at the very least a column) and if so, what DAX should I write? Using criteria [dataset] [username] [teamname]

[affan]

Hi @Anonymous

Use the Count with filter ALLEXCEPT

CALCULATE(COUNTA(Table1[username]),ALLEXCEPT(Table1,Table1[username]))
 

Then use this measure in the filter.

You can download the PBIX file here

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

[themistoklis]

@Anonymous

First create a measure which simply counts the number of teams.

e.g. No of Teams = COUNT(Table[Team_Name])

Then create a table object with users as dimension and No of Teams as a measure.

Then on Visualisations Pane go to "Visual Level Filter" and add the Measure there.

Go to filter type --> advanced filtering --> is greater than 2

[Anonymous]

Hi @themistoklis I've tried this - the thing is, the COUNT for number of teams, is simply counting the number of times that user appears in the data, and the count of team name so i've got hundreds coming up. I need it to count number of teams PER user....

[themistoklis · Accepted solution]

@Anonymous

If you add on the table the User as Dimension and the Measure (No of Teams) you will see the number of teams per user.

Make sure to click on the table and on the user field on Visualisations Panet o do right click on it and select Don't Summarise. This is actualy causing the problem.

 

Also change the formula to this one:

No of Teams = DISTINCTCOUNT(Table[Team_Name])

Workspace on this link:

[Anonymous]

Hi @themistoklis, As soon as I changed the formula to DISTINCTCOUNT it worked. I have both User and Number of Teams as Values and then as a Visual filter: is greater than 1. So thank you for helping me with this!! 🙂 However I now have a new problem. I want to add the team name to the table so we can see which team is right/wrong - or at least show the team name against the user, and have 'Number of Teams' just as a filter. But all the data disappears entirely when I try to add Team Name in to my Table. If I change the table to a matrix it works, but then I have a big messy matrix with 8 team names along the top and it's hard to analyse. I want it to look like a tabular pivot with the user name in one column, and team names as multiple rows in a second column. What are your thoughts on this...?

[themistoklis]

@Anonymous

Just Create a new table object right next to the previous one and have User as Dimension and Team Name next to it (both as dimensions). The on Visual level filters add again the restriction greater than 2.

Then you will see the team names per user.

I hope this is what you want?

[Anonymous]

It wouldn't show it all as I wanted so I went with a matrix in the end, but this was great thanks so much for your help!