Forum Discussion
Visualizing hierarchical data
Hello.
I have some problems visualizing data in a hierarchical format. I have tried to show the data in the tables below.
Process Hierarchies
| Process Name | Level 0 | Level 1 | Level 2 | Level 3 |
| Trigger_X | TriggerX | NULL | NULL | NULL |
| Master_Process | TriggerX | Master_Process | NULL | NULL |
| Sub_process | TriggerX | Master_Process | Sub_Process | NULL |
| Sub_sub_process | TriggerX | Master_Process | Sub_process | Sub_sub_Process |
Process Runs
| Process Name | Run Time |
| Master_Process | 2 |
| Master_Process | 3 |
| Sub_process | 1 |
| Sub_sub_process | 4 |
| Sub_process | 1 |
| Sub_sub_process | 4 |
When I try to visualize this data in Power BI, I have two problems:
1. there are blank rows on each level (see picture)
2. When I visualize the data, it is doing the aggregation of its children instead of calculating only at the level that it is on. For example, Power BI would calculate the average run time of master_process as (2+3+1+4+1+4)/6, but I want it to only calculate on the level that it is at, so it would instead be (2+3)/2
I am unsure if this is a data modelling issue or something that should be done in DAX.
4 Replies
Check your data model and try to get rid of Cross filters going in Both directions. Also, try replacing "null" or blanks with something meaningful like "not applicable" or "n/a".
Hi MNedix. Thanks for your reply. There is no cross filtering. The relationship is a simple one to many from the hierarchy dimension to the fact table.
Regarding the blanks: I have found that the blanks actually show the value that I want to be shown at the level above it. This leads me to the think that I need to change some things in the underlying sql, but not sure exactly
In my tests, I do not have the same problem as you are experiencing.
But I found another problem, the value of "Sub_process" in this Matrix does not correspond to the value in the "Process Runs" table.
My suggestion:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Yulia, thanks for your reply.
I have edited sub-process to sub-subprocess in the example data.
In the report you posted, I still see the same problem that I was describing. For example, I want Sub_process to show the average of (1+1)/2 = 1 and not including its children as it is currently doing: (1+1+4+4)/4 = 2.5.
I want the NULL row below sub_process to be the result of sub_process if that makes sense? (See screenshot)
