Forum Discussion
How to join/create relationship between two tables with non unique values?
Hi RikPatel,
Here you could make good use of dimensional modelling. This means you create tables in which the descriptive information is separated by topics into dimensions (e.g. in this case you could create a table with all unique dates and a table with all unique locations). you then create a relationship between these dimension tables and the tables containing your metrics (Fact tables). This way, whenever you select a location or date from your dimension table, all fact tables will be filtered. So you can plot metrics from both tables in the same graph.
Hope that helps a bit. Here's an example of a dimensional model from the microsoft learn page for reference: Tutorial: From dimensional model to stunning report in Power BI Desktop - Power BI | Microsoft Learn
Best regards,
Tim
Thank you Tim!
I created unique tables of all the other columns, (calendar table with booking_week_date and week num), a unique country table, unique city table, unique region table and unique sub region table and then established a one to many relationship with the two main tables using these unique tables.
I have played around with it and seems like it is working - I will do some more testing and then mark this as a solution.
Solution SageGreat to hear it's working!
As a follow-up, instead of multiple location tables you could also make one location table with city, sub-region, region, and country. And then create one relationship between that table and the tables containing metrics. Then you don't need to create a lot of relationships and can still slice 'n dice on all location levels.
Regards and good luck with the report!
Tim