Forum Discussion
Relational schemmes with 2 tables
Hello everyone.
Some time ago I made a post about data relationships for a hierarchical schema:
Now continuing with this topic I have 2 doubts:
1. How these relations should be created?
2. How can I count all the data related to each level per user?
This is the relation schema I'm working:
The data comes from 2 tables, [usuarios] (the black color guys) & [hijos] (the red color guys).
In the [usuarios] table there is a relation using fileds "CVE" & "CVE_padre", beacause a user can have another users under his command.
And the table [hijos] got the field "CVE_padre" wich connects to field "CVE" from [usuarios] table.
The way I solved this is:
1. Identify the top level users from table [usuarios]
2. create a new table for each relation/level
3. Combine the tables through mentioned fields
4. I need to combine [hijos] with his correspondant [usuario]. (I didn´t do this, because i guess this could be to heavy process, repeating the combining for each [usuario]).
This is the link to mentioned tables:
Thanks a lot.
The key to working with hierarchies in Power BI is flattening them in one table. The fact that in a table you'll have many repetitions is a good thing in the dimensional data modeling world. Your worry is totally ill-founded. You can read about how to handle parent-child hierarchies here: https://www.daxpatterns.com/parent-child-hierarchies/
4 Replies
The key to working with hierarchies in Power BI is flattening them in one table. The fact that in a table you'll have many repetitions is a good thing in the dimensional data modeling world. Your worry is totally ill-founded. You can read about how to handle parent-child hierarchies here: https://www.daxpatterns.com/parent-child-hierarchies/
Thanks, that's the solution, and that's a great post!!! .
The only thing that makes me doubt is that everything is made in DAX, but actually im working with 15k records and it still fast!
Thanks again!
What is the visual you are trying to create? Can you provide a sample depiction?
I may use matrix or hierarchy schemme or bar graph, also filtering by a common value. Listing the level 1 users to compare the total value for each one and drill down to level 2 users and so on.
