Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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,
dim_Product =
DISTINCT (
UNION ( VALUES ( Sales1[product code] ), VALUES ( Sales[product code] ) )
)
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! |
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi, there
pls refer to the post below, whose issue was almost identical to yours.
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!
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,
dim_Product =
DISTINCT (
UNION ( VALUES ( Sales1[product code] ), VALUES ( Sales[product code] ) )
)
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting