Simple Filter with a Many to Many Relationship

[Anonymous]

In a very simplified example, I have two tables. I have a two common fields between the two tables. Country and Fiscal Year. In one table are Sales Figures Actuals, and in the other table are Sales figures targets. The common fields between the two tables are Country and Fiscal Year,  so I have set a Many to Many relationship with the country field, as I dont believe or dont know how to create a link between two tables and two common fields (not even sure if needed, data modelling is not my strong point!)

How Can I display The correct Actuals with the correct Targets after filtering for Country and Fiscal year. I must be missing somethig simple in the tables and the relationships. 

This is an overly simplified representatin of my actual problem. Please see a link to my PBIX file with the two sample tables, and filter visualisations explaining problem and expected outcome.

Actuals v Targets PBIX File

Thanks in advance for assist. 

Ger

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

What you can do is make a table for years and countris (one each) then join those to your data tables and use the countries and years tables in your slicers.

I have attached your file updated with the new tables and relationships and the slicers changed to use the new fields for you to look at

[Anonymous]

Thank you @SteveCampbell  - A lot to read there, but it makes sense. I'll investigate what I can apply to my scenario.

@jdbuchanan71 - thank you this makes sense. I will definitely be able to apply this approach to my actual data. Thank you, I have accepted your solution.  

[SteveCampbell]

Set up a STAR schema:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

Appreciate your Kudos
Connect with me!

Stay up to date on  
Read my blogs on