Forum Discussion
Help with data model and relationships
- 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.
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.
thanks for you advise .. I didn't do exactly what you suggested, but it set the foundation to a solution ..
thank you very much!