New Column - Filter with just unique values

[JSaunders]

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:

UserID	Department	Groups
001	Finance	FR01, FR02, IT01
002	IT	IT01
003	Sales	S01, S02
004	Sales	S01, FR01

Group Table:

Groups	Departments Using this Group
FR01	Finance, Sales
FR02	Finance
IT01	Finance, IT
S01	Sales
S02	Sales

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.

[amitchandak]

@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