cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heytherejem Member
Member

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

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]
1 ACCEPTED SOLUTION

Accepted Solutions
themistoklis New Contributor
New Contributor

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

@heytherejem

 

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.

 image.png

 

Also change the formula to this one:

No of Teams = DISTINCTCOUNT(Table[Team_Name])

 

Workspace on this link:

 

7 REPLIES 7
themistoklis New Contributor
New Contributor

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

@heytherejem

 

 

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

heytherejem Member
Member

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

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....
Highlighted
affan Established Member
Established Member

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

Hi @heytherejem

 

Use the Count with filter ALLEXCEPT

 

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

Then use this measure in the filter.
 

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

 

Regards,

Affan

themistoklis New Contributor
New Contributor

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

@heytherejem

 

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.

 image.png

 

Also change the formula to this one:

No of Teams = DISTINCTCOUNT(Table[Team_Name])

 

Workspace on this link:

 

heytherejem Member
Member

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

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 New Contributor
New Contributor

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

@heytherejem

 

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?

 

heytherejem Member
Member

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

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! Man Happy