Build relatioship from SharePoint related lists: Data Model or Merge

[Anonymous]

Hi everyone, 

I'd appreciate your advice on the best method to build relationship between SharePoint related lists (parent-child)?

1. Should I pull them into PBI and behave like a sql table and build relationship in Data model panel? based on 1-many and have separate tables
2.  or I should just merge them (first 2 with a 3rd list and then with 4th, so on) and come with a big merged table that has nested tables?
   
   

I need to pull data from SharePoint lists that are related together in below fashion, I first loaded data, and expanded nested lists and then in modeling view built the relationship.

then later I ran into two issues:
1- my aggregations would get duplicated 
2- if there's no related value, since the join is inner join, lots of data would get filtered out

I'd appreciate your advice on the best approach to create the model and to calculate the correct SUM to avoid duplicates

[Anonymous]

I work with Sharepoint lists as my source of data model. Go with option 1. Think of each Sharepoint list as a dimension table and put the transactional values or IDs into a fact table. 

[Anonymous]

thanks, how about calculation of sum ? how can I prevent the sum from showing as duplicate in front of child items?

[Anonymous · Accepted solution]

You don't need to do any specific calculation, Power BI will do it for you as long as you model is adequate.

I replicated your tables in Power BI, see my model:

And end result:

Values being repeated in the matrix is expected behavior as of the current data that you have, since you want to have sub-domain and sub-class on the same chart, you don't have any way to relate which sub class belongs to which sub-domain, thus, Power BI applies the amount to all of them, but in the totals sum, it only adds values for domain. 

My recommendation for you would be to build a hierarchy table where you can map the sub-classes to the sub-domains.

Find here my working file.