Filter table if field contains value selected from another table

[mike_viz_lord]

TableA

Submission ID	Submission State
1	IL
2	IL;NC
3	NC
4	ND;NC;IL

TableB

Policy ID	Policy State
1	IL
2	NC
3	ND
4	IL
5	IL
6	IL
7	IL
8	IL

TableC

State Abbreviations

NC

ND

IL

I have created a visual that displays the Distinct Count of Submission ID from TableA and a separate visual that displays the Distinct Count of Policy ID from TableB. That is easy enough, but I also need to allow the user to filter both viz's by State. In TableA, however, Submission State is stored in the following manner, for example: 

"ND;NC;IL"

TableB stores Policy State individually as expected with one State per row. I created an additional table, TableC, that contains all State Abbreviations. TableC.State Abbreviation is used as my slicer on the page. I would like both visuals to filter based on the slicer selection. The first table would need to filter based on some sort of CONTAINS function. For example, if the user selects "IL" in the slicer, they would get a value of 3 from the first visual and 6 for the second visual. I created a calc as follows:

State Selected = CONTAINSSTRING('TableA'[Submission State],SELECTEDVALUE('TableC'[State Abbreviation]))

I added State Selected as a filter on my first visual and set it to True. However, when I select a State from the slicer, nothing happens to the value displayed in the top visual. It continues to show the default value of 4 (Distinct Count of Submission ID).

My plan was to apply a similar filter to the second visual but can't get the first one working. Any ideas?

[Anonymous · Accepted solution]

Hi @mike_viz_lord,

AFAIK, the slicer does not support advanced filter effects.

I think you can create a new table(not existed relationship to the raw table) as the source of the slicer, then you can write a measure formula with search/find function to apply filter effects and use it on your visual level filter.

Applying a measure filter in Power BI - SQLBI

If the above not help, you can share a sample pbix file to test.

Regards,
Xiaoxin Sheng