Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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