Forum Discussion
Dataflow : recreating a hierarchy from self referencing table
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.
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.
Thanks for sharing the link !
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.