Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi everyone,
I'd appreciate your advice on the best method to build relationship between SharePoint related lists (parent-child)?
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
Solved! Go to 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.
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.
thanks, how about calculation of sum ? how can I prevent the sum from showing as duplicate in front of child items?
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.
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |