Forum Discussion
First hierarchical nonblank value
Hello,
I've got a table with these columns: ParentID(text), ID(text), Attribute(text).
For each ID I need the first upper hierarchical non-blank Attribute to be returned in a calculated column.
eg. if Attribute of a row is blank, suppose that this row is logically at the 6th hierarchy depth level, the function will scan Attribute at its related level 5,4,3.... and so on, until it finds a non-blank value.
For now I know I have max 15 fixed depths of hierarchy, but I hope there's a function that can dynamically calculate what I need, regardless of the possible depths determined by the database structure.
Is it possible in PowerBI?
Thanks!
3 Replies
Should be, check out the PATH function.
- Yes, I tried, but I think it is a fundamental part of a more complicated function to be written.
First, in PBI the only way to implement parent-child hierarchies is to expand them to fixed-depth hierarchies. I hope you're aware of this. So you have to flatten the hierarchy. Once you've got this flat structure, it should be pretty easy to achieve what you want by very standard means.
For how to turn a parent-child hierarchy into a flattened one please find an article on www.sqlbi.com. Just type "parent child" into the search box and you should be presented with a handful of articles.
Also, please remember that you can unpivot your structure into a second (hidden) table and from such a table it should be easy to calculate what you want.
Best
D
