cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fabiomanniti
Helper III
Helper III

Filter all rows that cointain a substring

Hello, I have a table with different brands like

Nike | Nike_id

Adidas| Adidas_id

Mercedes| Mercedes_id

...

And then I have a table with users and their subscriptions in this way

User1 | Brand1_id/Brand2_id/Brand3_id/...

User2 | Brand4_id/Brand5_id/Brand6_id/...

...

 

Now, I cannot change the datasource because I have no control on the database but I want to know if I can do the following:

 

I want to create a filter-list with all brands and, based on what I click on, I get the user table filtered with all users that are subscribed to that brand.

So, in other words, if I click on Nike, I want all Users that have Nike_id as subsctring in the Subscription field

 

1 ACCEPTED SOLUTION

Ok I get it now. You need to delimit the column based on"/" and convert it into rows.

You can do it in Power Query --> Transform -->Split by delimter and chosing teh eblow settings:

PC2790_0-1652187098543.png

And then you can create a relationship as normal.

The outcome:

PC2790_1-1652187169180.png

Attaching the pbix file here for you to refer.

I suppose this is what you are looking for.

View solution in original post

6 REPLIES 6
rohit_singh
Solution Sage
Solution Sage

Hi @fabiomanniti ,

You can try doing something like below :

Assuming your tables look like this :

Brands

rohit_singh_0-1652186073564.png
Users

rohit_singh_1-1652186082643.png

In power query, you need to unpivot all the brand columns in the users table. This will give you something like

rohit_singh_2-1652186148359.png

Then in your report view, create a relationship between brands and users table

rohit_singh_3-1652186180834.png

 

You should now be able to filter users by selected brand.

rohit_singh_4-1652186207510.png

Please mark this answer as a solution if it solves your issue.

Kind regards,

Rohit

PC2790
Super User
Super User

If you have a separate table containing brand names, you can create a relationship with the main table based on the brand ID and create a slicer from the brand table.

You will be able to interact with the slicer and the data will get updated as per your selection.
if you can provide your sample data, can provide solution in detail

@PC2790 

 

This is exactly what I would like to do but if I create a relationship between the two tables based on 

Brands[Brand_id] and Users[Subscriptions] if guess it will look like fields with identical values, instead I need a relationship between two fields where one is contained into the other.

 

Hope it's clear

Sorry what do you mean by contained in another?

Can you give an example?

@PC2790 

Of course

 

Let's call
Table 1: Users

 

UserId Subscriptions

UserIdSubscriptions
wwer254d34525/221356/52356/13445/
sswr4663dwe355/221356/iio90/34525/

 

Table 2: Brands

 

Brand nameBrand id
Nike34525
Mercedes221356
Kappa52356

 

Now, I would like to create a relationship where the field Brands[Brend Id] is included in Users[Subscriptions] so that, if, for example, I select on my visual the Mercedes name, I get all users from Users table who have Mercedes id into their Subscriptions field

Ok I get it now. You need to delimit the column based on"/" and convert it into rows.

You can do it in Power Query --> Transform -->Split by delimter and chosing teh eblow settings:

PC2790_0-1652187098543.png

And then you can create a relationship as normal.

The outcome:

PC2790_1-1652187169180.png

Attaching the pbix file here for you to refer.

I suppose this is what you are looking for.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors