How to join/create relationship between two tables with non unique values?

[rikpatel1998v2]

Hi,

I need to create a graph that uses metrics from two different tables. Both the tables do not use any non-unique keys/columns, the only unique thing would be a combination of the different columns.

Here is an example of the schema of the two tables:

Table 1 has columns + some extra unneccessary columns: 

and some metrics lets say metric x y and z

Table 2 has similar columns + some extra unneccessary columns:

Week_commencing is the same as  booking_week_date in the first table while the rest of the columns represent the same thing too. Table 2 has some associated metrics a b c.

How can i establish a relationship between these two tables such that I can plot some simple charts that show e.g metric a and metric x on a line chart together, against the booking_week_date/week_commencing, while being filterable by the other columns in the tables (country, city, region etc).

I've tried creating a many to many relationship with the date field in both the tables, however, when applying a filter on e.g. destination country, it only works on the metric from which the destination country field was taken from.

Thanks

[timg · Accepted solution]

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

[rikpatel1998v2]

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.

[timg]

Great 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