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
JSaunders
Frequent Visitor

New Column - Filter with just unique values

Hey all,

It seems like I've done the hard part with this new column, I have a Users Table, which contains a column containing Groups it belongs to, as well as Department of the Users. The Group Table lists all of the individual Group Names.

What I need to do here is to list all of the departments that the users' belonging to this group are within.

Here's a view of the table setups:

 

User Table:

UserIDDepartmentGroups
001FinanceFR01, FR02, IT01
002ITIT01
003SalesS01, S02
004SalesS01, FR01

 

Group Table:

GroupsDepartments Using this Group
FR01Finance, Sales
FR02Finance
IT01

Finance, IT

S01Sales
S02Sales

 

Here's what I have so far. It gives me all of the correct information, although I need to de-duplicate it and can't find a way or place to put a distinct.

 

Departments Using this Group =
CONCATENATEX(

FILTER('all_users',

CONTAINSSTRING('users'[Groups],'groups'[Groups])

&&

NOT('users'[Department]="")

),

'users'[Department],", ")

 

Any help would be appreciated.

Many thanks.

 

1 REPLY 1
amitchandak
Super User
Super User

@JSaunders , One way, is to split columns into rows groups of table User

 

then you can try

Concatenatx(Table, Table[Department], ", ") 

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag

 

Concatenate Text- Measure, DAX Table, and Power Query Table: https://youtu.be/xAh3tz1qo24

 

One more way is, again try the same measure as above

Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c

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.

Top Solution Authors