cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Min_02
Regular Visitor

Relationships between tables by date

Hi,

 

I have three 3 tables: Sales1, Sales2 and Calendar Table. I need to link Sales1 and Sales2 by product code, and both Sales1 and Sales2 date columns to date column in Calendar Table.

 

The problem I'm facing is that a can create de relationship between Sales1 and Sales2 by product code, but not both date relationships. It's only possible to create one. When I try it, the following message appear: You can't create a direct active relationship between ... and ... because that would introduce ambiguity between tables.

 

Any idea how to link both sales tables to calendar one?

 

Thanks in advanced!

1 ACCEPTED SOLUTION

It's inferred from your description that you created a Many to Many relationship between Sale1 and Sale2; it's the last thing in the world of my recommendation even if it's supported by POWER BI! It's at the disposal of data modeling experts. A rule of thumb is to build a One to Many relationship whenever possible.

 

In your case, I suggest following steps,

  1. extract all unique product code from both Sale1 and Sales to form a dimention table this way,

 

dim_Product =
DISTINCT (
    UNION ( VALUES ( Sales1[product code] ), VALUES ( Sales[product code] ) )
)

 

  •  Relate dim_Product to Sales1 and Sales2 respectively, POWER BI can detect automatically as One to Many;
  • Now it's supposed to succeed in relating Calendar table to Sales1 and Sales2.

View solution in original post

5 REPLIES 5
edhans
Super User III
Super User III

You need to either create a single Sales Fact table, or have two Sales Fact tables linked only to the calendar table. You should not filter fact tables by each other. These are not SQL joins, but filter relationships.


You can filter virtually later in measures if you need using TREATAS() or other methods. If you could share some data and what you are trying to do we could be a bit more helpful. The goal though is to get as close to a Star Schema as possible, and those never have FACT tables that are filtered by each other.

 

Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
CNENFRNL
Super User III
Super User III

Hi, there

pls refer to the post below, whose issue was almost identical to yours.

https://community.powerbi.com/t5/Desktop/you-can-t-create-a-direct-active-relationship-between-Surve...

Hi @CNENFRNL , I saw the post you sent me. Based on that issue, in which table should I create de measure? How do you think the measure should be? What are the next steps after creating the measure? Thanks!

It's inferred from your description that you created a Many to Many relationship between Sale1 and Sale2; it's the last thing in the world of my recommendation even if it's supported by POWER BI! It's at the disposal of data modeling experts. A rule of thumb is to build a One to Many relationship whenever possible.

 

In your case, I suggest following steps,

  1. extract all unique product code from both Sale1 and Sales to form a dimention table this way,

 

dim_Product =
DISTINCT (
    UNION ( VALUES ( Sales1[product code] ), VALUES ( Sales[product code] ) )
)

 

  •  Relate dim_Product to Sales1 and Sales2 respectively, POWER BI can detect automatically as One to Many;
  • Now it's supposed to succeed in relating Calendar table to Sales1 and Sales2.

View solution in original post

+ 1 on @CNENFRNL 's recommendation to avoid M2M relationships. MS recommends it! Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors