I’m trying to create a relationship between two tables.
One table (Sales Data Table) is a fact table with sales volume information. There is no ‘date’ column in this table, only [Year], [Month], [Week of Year] columns in terms of calendar elements. There are also product codes, accounts, and sales volume info in the table.
Second table (Calendar Table) is a lookup table which is a simple calendar table with [Year] & [Week of Year] concatenated and has ‘Starting Date’ for each week. The 3rd image inserted below show an excerpt of this table to give you an idea.
I’m trying to create a relationship between the two tables so there’s ‘date’ info for the Sales Data Table, so I can do time comparison analysis (e.g. year over year, year to date).
I created a column for each table that has [Year] & [Week of Year] concatenated (e.g. 20191, 20192, 20193, … , 201952), and tried to create a relationship with these columns to use the ‘date’ in the Calendar table.
But the problem is it only allows ‘Many to Many’ relationship and not ‘1 to Many’.
I double checked my Calendar Table so it only has unique row of [Year] & [Week of Year].
I believe it’s normal to have numerous rows of same [Year] & [Week of Year] in the Sales Data Table.
I also checked that there’s no ‘NULL’ values in the [Year] & [Week of Year] columns in both tables.
Could anyone think of what might be the issue that’s causing Power BI to only allow ‘Many to Many’ relationship and not ‘1 to Many’?
Thank you for your tips and advices in advance.