How to filter/slice for multiple string values from one string list

[Anonymous]

Hi all - not exactly sure how to frame this question but will try my best:

I am trying to create a slicer that allows the user to select an industry and then it will filter out the table for that industry, wherever it's identified. 

Here is the problem though, illustrated through an example: 

The Subtopic/industry column contains multiple industries in one cell, i.e. "Agriculture; Construction; Manufacturing" whereas the first file Column1 has all industries listed, row by row as seen below. 

How can I get Power BI to capture multiple string values from one selection of "industry"?  Importantly, when I delimit the column "subtopic/industry" (by semicolon obviously), I still cannot achieve the result I want because I cannot create multiple active relationships, i.e. Industry, Industry.1, .2, etc. and connect to Column 1 file. 

My ideal result is to be able to select, for example, "Supply Chain" and Power BI would then be able to identify the "Transportation; Supply Chain" and it filter the table on my dashboard for anything that has this string, regardless. 

Should I remove the Column1 file altogether and try to create a filter from the Subtopic/Industry only? Is there a DAX expression or a simpler way? 

[v-cgao-msft · Accepted solution]

Hi @Anonymous ,

You might consider the second method that lbendlin mentioned.

suppose we have:

Table1:

Table2:

relationship:

Please new a measure like:

Filter = 
VAR _industries = SELECTEDVALUE('Table1'[Column1])
VAR _filter = SEARCH(_industries,MAX('Table2'[Subtopic/industry]),,BLANK())
RETURN
_filter

and use it as table visuals filter like this:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

All - is anyone else aware of how to filter multiple selections based on the conversation above? See below:

"I have a quick question regarding this and figured I would ask on this thread since you know what I was doing. 

The filter works, but only for single selections. When I try to ctrl + click on multiple industries, it just shows the entire table again and does not filter for the selections. See picture attached. I selected automotive and agriculture, but you can see that Retail and others appear. Any thoughts on fixing this? "

[v-cgao-msft · Accepted solution]

Hi @Anonymous ,

You might consider the second method that lbendlin mentioned.

suppose we have:

Table1:

Table2:

relationship:

Please new a measure like:

Filter = 
VAR _industries = SELECTEDVALUE('Table1'[Column1])
VAR _filter = SEARCH(_industries,MAX('Table2'[Subtopic/industry]),,BLANK())
RETURN
_filter

and use it as table visuals filter like this:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous · Accepted solution]

Thanks Gao - I copied your formula and attempted to make a replica but it does not seem to filter anything. 

Filter = 
VAR _industry = SELECTEDVALUE('Industry'[Column1])
VAR _filter = SEARCH(_industry,MAX('Data Assets - Exploration'[Subtopic/Industry]),,BLANK())
return
_filter

I then deleted the one-to-many relationship between both tables. 

You can see that when I select "Supply Chain" for example, it does not filter the second table. Any ideas? I also did not create a new table per se, but rather just built the table from the selected "Data Assets" table. 

[v-cgao-msft]

Hi @Anonymous ,

Did you drag the [Filter] indicator into the bucket of the table visual filter, set it to "Not Blank" and apply it?

If this does not work, please consider providing a sanitized .pbix file for testing.

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

Gao - I have a quick question regarding this and figured I would ask on this thread since you know what I was doing. 

The filter works, but only for single selections. When I try to ctrl + click on multiple industries, it just shows the entire table again and does not filter for the selections. See picture attached. I selected automotive and agriculture, but you can see that Retail and others appear. Any thoughts on fixing this? 

[Anonymous]

Gao - this worked perfectly! I had thought it was auto added to the filtering but I was wrong.

Thank you for the help!!

[lbendlin]

The easiest option would be to allow users to change filter types, add that column to the report level filters, and to teach your users how to do advanced filtering via the search box or via "contains".

A more complex option would be to use a disconnected table and a measure that is then used as a visual filter.