Usage of Power BI Interactive visualisations with Parent-Child dataset relations

1
votes

I have been meddling with Power BI for almost a week now. It seems like a powerful tool, when you get to know your way around it at least..

I would like to be able to see the sum of Therapists, Admins and Citizens, based on all subgroups for the currently selected group.

Here is my example: enter image description here When i select a Group (resembling a customer group) in the Drill Down Donut Chart, i want so see admins, therapists, and citizen count for all subgroups in the selected group, shown in the Clustered Column Chart. However I only get the users which are in the selected group, and not the users in sub groups.

I have created measures for Admins, Therapists & Citizens to get the count based on TemplateLevel (which is resembling the role of the user: enter image description here All measures are written in the same fashion, using different TemplateLevel(s).

Here is the three measures used in the Column Chart: enter image description here

In my DataSet, i have the table UserGroup: enter image description here IdPath and NumLevels is an attempt to use parent-child reference, which i did not get to work properly, so dont mind that. I expected that Power BI's interactive system would be able to handle Parent/Children references, as is the case with UserGroup[Id] and UserGroup[UserGroupParentId]. My initial thoughts was to just add GroupName as Category for each level of SubGroup available (Owner -> Customer -> Therapist -> Citizen).

Owner group id is 27 and always will be so that's why the drill down donut chart is filtering groups with no such parent it, to show the customer groups.

The DataSet for the report is from a test database migrated to an Azure SQL Server.

Any suggestions are given the warmest welcome!

Kindly Regards Kalrin

1
azure-sql-databasepowerbiparent-childdax
I don't think it's possible with your current design. You need to flatten the hierarchy first: daxpatterns.com/parent-child-hierarchies – RADO

1 Answers

1
votes

Power BI (or more precisely, the Tabular Model underlying Power BI) does not support parent/child relationships. You have to transform/flatten the hierarchy to construct a table that holds the columns of all the levels of the hierarchy:

| Id    | Owner     | Customer  | Therapist | Citizen  | Group    |
| ----- | --------- | --------- | --------- | -------- | -------- |
|     1 | ownerX    |           |           |          | 1        |
|     2 | ownerX    | cust1     |           |          | 1        |
|     3 | ownerX    | cust1     | tpA       |          | 1        |
|     4 | ownerX    | cust1     | tpA       | cit100   | 1        |
|     5 | ownerX    | cust1     | tpA       | cit101   | 1        |
|     6 | ownerX    | cust1     | tpB       |          | 1        |

The above is a flattened hierarchy which is also ragged (you can have parent items with no child items).

This pattern describes how we can use DAX to construct a flattened hierarchy, but typically it is a best practice to flatten your data on the database side, before loading the table into Power BI (this can be done using recursive CTEs in SQL).