Forum Discussion

julsr's avatar
julsr
Continued Contributor
1 year ago
Solved

Error when creating relationships: Redundancy and ambiguous paths between tables

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!

  • 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

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

    • julsr's avatar
      julsr
      Continued 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. 

      • lbendlin's avatar
        lbendlin
        Super User

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