Many to many relationships

[Anonymous]

Dear all,

I created an excel that holds the hierarchy: country, region and then product.

Then, for each year I created in one excel, monthly, weekly and daily production data. I plan on reporting two years, so 2022 and 2023. Those years have the same products, the same regions and the same countries. Maybe year 2022 can have one additional product than the year 2023.

What is the best way to be able to create a relationship between years? So that the PowerBI end user can filter through years, months and dates from a single filter?

Thanks in advance and have a nice day!

[Anonymous]

@HughLa Do you maybe have a solution to creating relationships, for example:

Monthly 2022 and Monthly 2023 contain the same column called Daily production. Since I would like to create a table that would list both Daily production's  - I would need to drag and drop two different columns. How can I merge them, or create a relationship between them? 

[Anonymous]

Actually, I understood it better now and tried your version. It worked. Thank you!

[Anonymous]

@HughLa Thank you, I think I understand. But the biggest problem is that my daily table for year 2022 has around 40 000 rows. So I believe, combining a few years (plus every table has calculations) would be too much for Excel.

[HughLa · Accepted solution]

Hi @Anonymous 

The best practice would be have all of those yearly, monthly and weekly tables in one table and create a date dimension table with dates from 2022 to 2023. Then have the relationship between the date table and your transactions table.

This will allow you to drill down and expand into the date periods.

Hope this makes sense.

Hugh