Forum Discussion
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:
| 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.
1 Reply
- amitchandak
Super User
Anonymous , 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
