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

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 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).
two tables

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].
calendar table

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.

@ScottRoh,

Welcome to the forum – great to have you here!

I would suggest abandoning the route you’re on and taking a completely different path, since the way you’re trying to connect your date and fact tables is going to make doing the type of time intelligence analysis you want to do difficult to impossible. Instead, I would recommend:

  1. Creating a proper date table at the daily level. You can go with the standard date table, or if you want to add some additional enhancements that will make time intelligence analyses even easier, go with the extended date table. References to both provided below:
  1. Because you’ve got a granularity mismatch (i.e., dates at the daily level, sales at the weekly and monthly level) you can’t use the traditional one-to-many physical relationship. So instead of using a many to many relationship (which is chockablock with problems), create a virtual relationship between your date table and your fact table at the matching granularity level (weekly or monthly or even yearly, depending on the needs of your analysis) using the TREATAS function, which will allow you to do proper time intelligence.

Here’s a great explanation of TREATAS:

I hope this is helpful.

  • Brian
1 Like

Hi @ScottRoh, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!