Forum Discussion
Dataflow : recreating a hierarchy from self referencing table
Hi
I'm setting up a new dataviz project for my company and I have a use case that I want to submit to this very active and great community 🙂
Context : all our data are stored in a postgre db. I connect to this db and load / transform data through a dataflow. Then my users will create reports based on a dataset that will be refreshed by the dataflow I mentionned above.
Problem I face : among the tables in the postgre db we have a table that contains the categories for our product. The table is self referencing with a structure like this :
| category_id | category_name | category_parent_id | category_parent_name |
| C001 | name001 | null | null |
| C002 | name002 | C001 | name001 |
| C003 | name003 | C001 | name001 |
| C004 | name004 | C002 | name002 |
| ... | ... | ... | ... |
This creates a hierarchy of categories in which we publish our products. The hierarchy can be up to 4 levels. For instance :
Fruits & vegetable > Fruits > Apples > Golden
Fruits & vegetable > Fruits > Mango
We need this hierarchy in powerBI in order to group metrics per category (for instance, measuring the sales for one level of the category)
My first idea for a solution :
My first guess is to use the transformation capabilities of dataflows to recreate a table that contains the hierarchy :
| Level 1 | Level 2 | Level 3 | Level 4 | category_id |
| name001 | null | null | null | C001 |
| name001 | name002 | null | null | C002 |
| name001 | name003 | null | null | C003 |
| name001 | name002 | name004 | null | C004 |
| ... |
|
If I can recreate this table, then I should be able to use it in powerBI reports, shouldn't I ?
My questions are the following :
- Is this the best solution to achieve what I want ? should this transformation be done in the dataflow or elsewhere in powerBI ?
- If the dataflow is the right tool to do it, how can I recreate the table from the self referencing category table ?
Thanks for your help !
GĂ©rald
The DAX functions are created specifically for this purpose, for me I would stick with that.
You could replicate this in your dataflow, or even in your warehouse by creating a flattened hierarchy. If your hierarchy will have the same amount of levels it would make sense to do it at warehouse and store as a table for the sake of using in multiple report solutions.
6 Replies
Hi,
Please read through this.
https://learn.microsoft.com/en-us/dax/understanding-functions-for-parent-child-hierarchies-in-dax
In addition you may want to take a look at 'Hierarchy Slicer' visual from the market place which offers more features than the standard microsoft visual
Hi ajohnso2
Thanks for your reply. I read the document you shared with me. I think DAX could be a good solution but I'm wondering if I'd rather do the transformation in the dataflow itself.
So i tried the following solution :
- In power query M, I created a function that parses the entries in the categories db and get all the ancestors for one category. I don't think there is a function similar to the ones in DAX so I had to create one. It's a recursive function that navigates in the hierarchy and collects all the ancestors of one category
- I create as many columns as needed and store the values of ancestors in each column
What do you think of this solution ? Should I keep it or aim for a transformation in the dataset with DAX ?
I find it hard to decide what's the best implementation.
BR
GĂ©rald
The DAX functions are created specifically for this purpose, for me I would stick with that.
You could replicate this in your dataflow, or even in your warehouse by creating a flattened hierarchy. If your hierarchy will have the same amount of levels it would make sense to do it at warehouse and store as a table for the sake of using in multiple report solutions.
