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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.