Filter all rows that cointain a substring

[fabiomanniti]

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

[rohit_singh]

Hi @fabiomanniti ,

You can try doing something like below :

Assuming your tables look like this :

Brands

Users

In 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

[PC2790]

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

[fabiomanniti]

@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

[PC2790]

Sorry what do you mean by contained in another?

Can you give an example?

[fabiomanniti]

@PC2790 

Of course

Let's call
Table 1: Users

UserId Subscriptions

UserId	Subscriptions
wwer254d	34525/221356/52356/13445/
sswr4663d	we355/221356/iio90/34525/

Table 2: Brands

Brand name	Brand id
Nike	34525
Mercedes	221356
Kappa	52356

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

[PC2790 · 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:

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.