Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

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

@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.

 image.png

 

Also change the formula to this one:

No of Teams = DISTINCTCOUNT(Table[Team_Name])

 

Workspace on this link:

 

View solution in original post

7 REPLIES 7
affan
Solution Sage
Solution Sage

Hi @Anonymous

 

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
Community Champion
Community Champion

@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
Not applicable

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

@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.

 image.png

 

Also change the formula to this one:

No of Teams = DISTINCTCOUNT(Table[Team_Name])

 

Workspace on this link:

 

Anonymous
Not applicable

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...?

@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
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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