Trying to create a Relationship but could not get '1 to Many'

[ScottRoh]

Hello,

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 simple calendar table with [Year] & [Week of Year] concatenated and has 'Starting Date' of 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.

[v-lili6-msft · Accepted solution]

hi  @ScottRoh 

See the screenshot, there are some duplicate value in the Calendar Table,

Just remove the duplicate value then it will work well.

Regards,

Lin

[ScottRoh]

Thank you, Lin.  Can't believe I missed them, it's pretty obvious.

[Greg_Deckler]

I would create the relationship between your Date column in your Calendar table (unique hopefully) and your Week Starting Date in your fact table. That will be 1 to many.

[ScottRoh]

Thank you, Greg.

But the problem is that there is no Week Starting Date in the fact table (Sales Data Table). The reason I created the Calendar table is to have Date info for the fact table. In the fact table, there are only Year, Month, and Week of Year data.

If you can think of any other reason why [Year] & [Week of Year] concatenated columns in both tables cannot be linked as 1 to Many relationship, I'd appreciate it.  Thank you.