topic Re: First hierarchical nonblank value in DAX Commands and Tips

First hierarchical nonblank value

AGo — Wed, 08 Apr 2020 18:51:20 GMT

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!

Re: First hierarchical nonblank value

Greg_Deckler — Wed, 08 Apr 2020 20:40:53 GMT

Should be, check out the PATH function.

Re: First hierarchical nonblank value

AGo — Wed, 08 Apr 2020 22:42:56 GMT

Yes, I tried, but I think it is a fundamental part of a more complicated function to be written.

Re: First hierarchical nonblank value

Anonymous — Sun, 12 Apr 2020 13:02:49 GMT

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