Help with data model and relationships

[truttafisker]

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 an overlook of the model and the relationship, but I need a relationship between the Date-table and PriceList line (the blue/red dotted line) .. 

But when I add the relationship between these two tables, I'm told it's ambigiously .. 

Are there anyone who have an idea on how to solve this ?

[MattAllington · Accepted solution]

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. 

[truttafisker]

thanks for you advise .. I didn't do exactly what you suggested, but it set the foundation to a solution .. 

thank you very much!

[Brunner_BI]

aren't you retired? or is this what retirement looks like? 🤣

[MattAllington]

😜. Retirement for me is answering questions that I want to answer as opposed to answering questions I have to answer in order to earn a living, whether I want to answer them or not. 🙂

[Brunner_BI]

That is a great answer!