Error when creating relationships: Redundancy and ambiguous paths between tables

[julsr]

Hello!

I have 6 tables:

1. Central dates table with columns:
   • Date
   • Year
2. Sales table with columns:
   • Date
   • SellerID
   • Amount
3. Projections table with columns:
   • SellerID
   • Date
   • ConcatField (SellerID - Date)
   • ProjectedValue
4. ProjectionsRemaining table with columns:
   • SellerID
   • RemainingProjected
5. ProjectionsMaster table with columns:
   • ProjectionID
   • ProjectionName
   • Active
   • ApprovedDate
6. 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!

[lbendlin]

Do not join fact tables directly. Control them via common dimension tables (in your case Dates)

[julsr]

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. 

[Ashish_Mathur]

Hi,

Create a single column table with unique ID's and build a relationship between the ID column of your Fact tables with the ID column of the single column table.

[lbendlin]

Don't join them. Use TREATAS to project filters across them.

[julsr]

Thanks! If I have 100 measures that must be affected by this ProjectionID (to ensure they're using the correct values based on this ID which will be modified through a Slicer Visualization), should I modify all 100 measures to use the TREATAS function?

[lbendlin · Accepted solution]

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.