Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
CNENFRNL
Community Champion
Community Champion

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.

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

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
Community Champion
Community Champion

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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!

CNENFRNL
Community Champion
Community Champion

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.

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

+ 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors