Relational schemmes with 2 tables

[elflakoconk]

Hello everyone.

Some time ago I made a post about data relationships for a hierarchical schema:

https://community.powerbi.com/t5/Translated-Spanish-Desktop/Esquema-jerarquico-cuenta-incompleta-de-...

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:

https://legazpyy-my.sharepoint.com/:x:/g/personal/alberto_legazpy_com/EWDbHPlW25RPkB7AUImvqGMBd26q6q...

Thanks a lot.

[PaulDBrown]

What is the visual you are trying to create? Can you provide a sample depiction?

[elflakoconk]

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.

[daxer-almighty · Accepted solution]

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/

[elflakoconk]

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!