Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
mike_viz_lord
Frequent Visitor

Filter table if field contains value selected from another table

TableA

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

 

TableB

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

 

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?
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.