Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
@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
User | Count |
---|---|
86 | |
83 | |
67 | |
61 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |