Sync Filtering Between Tables

[StanHost12]

Hello!

I'm relatively new to Power Bi and I've been learning as I go with things, but I've hit a roadblock that I seemingly can't find any answers out on the internet.

Background:

I am making a report that has two different tables that can be related to each other through 3 columns, a Region, a Sub Region, and a Category. I can only have 1 active relationship at a time and I am trying to Sync Slicers across pages to get more in depth data trends on the Survey Response Page when I set the filters on the original page showing performance measures.

I need the graphs on the Survey Response Page to filter the Region, Sub Region, and Category so I can get the graphs to show the numbers/percentages for that specifc Category from that Sub Region within that Region.

Relationships: Category-Category, Sub Region-Sub Region, Region-Region

My issue:

(Keep in mind the 'Performance Measure' slicer/filter does not need to be synced, also all the relationships are set to 'Both' as 'Single' direction seemingly doesn't help me here in any way)

When active relationship is the Region it does NOT filter the Sub Region OR the Category.

 

When active relationship is Sub Region it filters both the Region and Sub Region BUT NOT the Category so the graphs add all the numbers/data of all the Categories together.

When active relationship is Category ONLY the Category gets filtered across pages through thesync filters. Numbers on graphs reflect the data/totals for that Category across ALL Sub Regions and Regions.

Seemingly the only solution I have found to show data I want to carry over from the filters is by making the active relationship
between the Sub Regions and then making another seperate filter/slicer on the Survey Response Results
page to filter the Category (from the Survey Results data table) after the Region and Sub Region (from the original Performance Measures page and table) have been filtered.

Or putting Category on the legend of a graph showing all the Categories so you can choose which one to look at. This is not ideal though.

I would love to be able to just set the filters for the Region, Sub Region, and Category on the original Performance Measures page and have it filter all 3 creating the graphs that look like this (below) without needing another filter on the Survey Results Page.

Does anyone have a possible solution to my issue here? I will link the test data file as well if anyone wants/needs to work with it physically to find a solution. (link to file has been removed as it's been solved)  Thank you!

 

[MNedix]

Heya,

On my opinion you should definitely get rid of the Many-to-Many and Bidirectional relationships, to me they are the Twilight Zone of PowerBI (the results vary depending on the positioning of the planets).

I would simply create two dimension tables: one with the Category and the second with Region and Sub-region and use them to create proper relationships.

[Selva-Salimi · Accepted solution]

Hi @StanHost12 

I think an easiest way to solve your problem is to create a column, by concatenating these three columns in both tables and define your relationship based on created column! . (remmember to use some unique characters between column concatenate to avoid mismatching)

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[StanHost12]

Thanks for the help @Selva-Salimi !

I had seen this method online but hadn't applied it correctly or known it could be used in this scenario. From what I can see this method is working (so far) for both the test data and my actual report!