Heinous many to many data structure is Power BI the right tool?

[gazpage]

So I am trying to do some analysis on a portfolio of loans. The structure of the data is such that it is a web of many to many relationships.

I have data tables for, among others:

Borrowers
Loans
Guarantees
Units
Properties

The Units have a many to one relationship with the properties, but everything else is fair game. For example a borrower can have multiple loans, and a loan can have multiple borrowers. A guarantee can cover multiple units and multiple loans etc etc

I have dealt with many to many relationships before, but they were still basically a fact and dimension table structure. If I naively just create relationships wherever one exists I am going to get in a real mess.

Ultimate aim is to stratify the data somehow and also do some calculations, for example sum up the lower of a loan value and the assets it is tied to by guarantee. Is Power BI even the right tool for this? I'm looking for general advice rather than specific.

Thanks
Gary

 

[Matt Allington]

Here is no one correct design. It may be helpful to cnsolidate tables, maybe not - it depends. Many 2 many is a valid pattern and there is no reason not to use it. You may find my article here helpful. https://exceleratorbi.com.au/many-many-relationships-dax-explained/

 

[gazpage]

Thanks. Great article.