Forum Discussion
Relationships between tables by date
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,
- 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.
Hi, there
pls refer to the post below, whose issue was almost identical to yours.
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,
- 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.
+ 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
