Forum Discussion
Error when creating relationships: Redundancy and ambiguous paths between tables
Hello!
I have 6 tables:
- Central dates table with columns:
- Date
- Year
- Sales table with columns:
- Date
- SellerID
- Amount
- Projections table with columns:
- SellerID
- Date
- ConcatField (SellerID - Date)
- ProjectedValue
- ProjectionsRemaining table with columns:
- SellerID
- RemainingProjected
- ProjectionsMaster table with columns:
- ProjectionID
- ProjectionName
- Active
- ApprovedDate
- ProjectionsYTD table with columns:
- Date_conc
- ProjectionID
- SellerID
- YTDValue
Current joins:
- Dates -> Sales (by Date)
- Sales -> Projections (by ConcatField)
- Sales -> ProjectionsRemaining (by SellerID)
- Dates -> ProjectionsYTD (Date_conc)
- ProjectionsMaster -> Projections (ProjectionID)
- ProjectionsMaster -> ProjectionsRemaining (ProjectionID)
The issue arises when trying to create the relationship ProjectionsMaster -> ProjectionsYTD into this logic (ProjectionsMaster is a new table that I want to integrate to be used to filter all the projectionsIDs from my multiple tables but it must be affected by the Date in my DateTable). I need ProjectionsMaster to filter the ProjectionsYTD table based on its values, but PowerBI warns this will cause redundancy and create ambiguous paths between tables. What solutions are available? I've attached a sample PowerBI file demonstrating my current scenario (https://drive.google.com/file/d/1HWMYlFSU7xXNyWBj2frU0vFhEfLT6fkn/view?usp=sharing)
Thanks!
If you have 100 measures then you have about 80 measures too many. Consider using Calculation Groups if your measures do roughly the same thing.
Other than that, yes.
6 Replies
- lbendlinSuper User
Do not join fact tables directly. Control them via common dimension tables (in your case Dates)
- julsrContinued Contributor
Thanks for your reply!
Yes, but I also need them to be controlled via fact table (projection ID). This projection ID could have the same year (two IDs in the same year) so if I use only the date, this will not work as expected.- lbendlinSuper User
Don't join them. Use TREATAS to project filters across them.