Forum Discussion

truttafisker's avatar
truttafisker
Helper II
2 years ago
Solved

Help with data model and relationships

Hi there I have a problem with ambigious relationship .. and I can't figure how to fix this .. so I hope that someone out there could guide me in the right direction. in the picture you can see...
  • MattAllington's avatar
    2 years ago

    This looks to me like a relational database schema. My advice is to flatten this into a star schema as best you can.  Eg, the bonus entry tables look like they could be flattened. I also suggest appending the invoice header and credit header into a single table.  Just add a new column to identify each as invoice or credit. Same for the details. 

    I can see that you have a pricing table. I suggest you consider writing the data during ETL into the transaction tables. From what I can see, you are storing the "prices" in the model. A different approach would be to write/store the price against the transaction during data load. If you can do this, everything would be a lot easier.

     

    if you make these changes, I think the problem goes away. What you need to know is that relationships in PBI re actually "instructions" for the model to propagate filters between tables. If there is more than one path for filter propagation between tables, then "ambiguity" sets in. By flattening and simplifying the model during load, you can make it work.