One slicer to filter 2 data tables on a single report page

[Anonymous]

I have a single excel spreadsheet with 2 data tables. One is time tracking for projects and the other is vendor/licensing costs. This contains summary report data from multiple project managers across multiple charters.

I need a single report page to show filtered data by project charter and I want to use single slicer so management can drop down and select a charter and the two tables below will be filtered based on that slicer to show the respective time tracking and software cost tracking from each table for that selected charter.

I don't have any relationships established and I am using the web app with PowerBI Pro. Do I need to establish a many to many relationship for the project charter name (and how can I do this in the web app) or create a 3rd table in PowerBI (which I cant figure out how to do in the web app) that has a 1 to 1 relationship with the other tables to use as the slicer table?

[v-alq-msft]

Hi, @Anonymous 

I'd like to suggest you refer the following guidance:

• Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column
• Add a bridging table to store associated entities
• Create one-to-many relationships between the three tables
• Configure one bi-directional relationship to allow filter propagation to continue to the fact-type tables
• When it isn't appropriate to have missing ID values, set the Is Nullable property of ID columns to FALSE—data refresh will then fail if missing values are sourced
• Hide the bridging table (unless it contains additional columns or measures required for reporting)
• Hide any ID columns that aren't suitable for reporting (for example, when IDs are surrogate keys)
• If it makes sense to leave an ID column visible, ensure that it's on the "one" slide of the relationship—always hide the "many" side column. It results in the best filter performance.

For further information, please refer the following official documents.

Many-to-many relationship guidance 

Apply many-many relationships in Power BI Desktop 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

Hi there

Whilst you could use the solution above I would recommend not using MANY-MANY and bi-directional relationships this has a big chance of getting the data wrong and the measures wrong too.

Best practice would be to create a star schema model to get this working and a lot easier to get out the data you need.

[GilbertQ]

Hi there

You would have to model this in Power BI Desktop.

What you would then need to do is to create a new table which has got the unique list of "project charter" and then create a one to many relationship to your two tables.

Finally you would then use this new table as your slicer drop down, which would then filter both tables.