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