Visualizing hierarchical data

[BenjaminBoothBW]

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. 

[v-xuxinyi-msft]

Hi @BenjaminBoothBW 

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 Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BenjaminBoothBW]

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) 

[MNedix]

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".

[BenjaminBoothBW]

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