Forum Discussion
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
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:
And then you can create a relationship as normal.
The outcome:
Attaching the pbix file here for you to refer.
I suppose this is what you are looking for.
6 Replies
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 detailThis 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?
Hi fabiomanniti ,
You can try doing something like below :
Assuming your tables look like this :
Brands
UsersIn power query, you need to unpivot all the brand columns in the users table. This will give you something like
Then in your report view, create a relationship between brands and users table
You should now be able to filter users by selected brand.
Please mark this answer as a solution if it solves your issue.
Kind regards,
Rohit
