cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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

two tables.png

 

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'.

relationship error.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I double checked my Calendar Table so it only has unique row of [Year] & [Week of Year].

calendar table.png

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

hi  @ScottRoh 

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

Just remove the duplicate value then it will work well.

1.JPG

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

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

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
New Member

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

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.

Highlighted
Community Support
Community Support

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

hi  @ScottRoh 

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

Just remove the duplicate value then it will work well.

1.JPG

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
New Member

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

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors