Dataflow : recreating a hierarchy from self referencing table

[Gérale-Récolte]

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 :

1. Is this the best solution to achieve what I want ? should this transformation be done in the dataflow or elsewhere in powerBI ?
2. 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

[ajohnso2]

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

[Gérale-Récolte]

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 :

1. 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
2. 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

[ajohnso2 · Accepted solution]

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.

[Gérale-Récolte]

Hi @ajohnso2 

I think that I'd rather put it in the dataflow when transforming data rather than in the dataset or any report with DAX. Indeed, I feel that I should prepare data as early as possible in the data loading process and then make it available to all users with a shared syntax. This would allow us to ensure consistency through all reports.

[ajohnso2]

https://www.youtube.com/watch?v=a1PAOeonCcc 

This video explains the solution in great detail

[Gérale-Récolte]

Thanks for sharing the link !