topic Re: PATH function extract sub-level 2 hierarchy in DAX Commands and Tips

PATH function extract sub-level 2 hierarchy

witbi — Tue, 19 Jan 2021 20:06:41 GMT

I'm trying to use PATH functions to generate a flat table for filtering. Examples online seem to start with an explicit hierarchy in the table. I'm stuck on extracting the sub-level 2 hierarchy from my data (see below). Am starting out with Power BI so if anyone can provide some guidance that would be great.

Thanks in advance

Re: PATH function extract sub-level 2 hierarchy

lbendlin — Wed, 20 Jan 2021 02:04:14 GMT

LOOKUPVALUE is most likely not what you want. Please provide sample data in usable format (not a picture) and show the expected outcome.

Re: PATH function extract sub-level 2 hierarchy

Anonymous — Wed, 20 Jan 2021 06:00:50 GMT

Hello witbi
Are you looking for this?
Yes, try this. For this, you need to add the index column.
You can easily add the index column in the power query editor.

Re: PATH function extract sub-level 2 hierarchy

witbi — Wed, 20 Jan 2021 17:54:44 GMT

Yes Anonymous an Index makes sense

Re: PATH function extract sub-level 2 hierarchy

witbi — Wed, 20 Jan 2021 18:32:07 GMT

Hi Ibendlin,

Column 1-4 is the initial table. As Anonymous pointed out I was missing the index.

"Expected Hierarchy" is what I am looking to calculate so I can run PATH functions like this to calculate Level 1, Level 2, Level for use in a slicer:

Level 1= LOOKUPVALUE(Expected_result[Description],Expected_result[Index], PATHITEM(Expected_result[Path],1,1))

However, I've tried to calculate the column "Calculated Hierarchy" to resemble the "Expected Hierarchy" but it has become overly complicated. I would appreciate any ideas on creating a calculated column.

Calculated Hierarchy =

VAR _l1=IF(LEN(Expected_result_test[Ref])=Expected_result_test[Pathlength],Expected_result_test[Index])

VAR _delimiter1=SUBSTITUTE(Expected_result_test[Ref],".","|",1)

VAR _l2_len=SEARCH("|",_delimiter1,,LEN(_delimiter1))-1

VAR _parent=LEFT(_delimiter1,_l2_len)

VAR _index= IF(ISERROR(LOOKUPVALUE(Expected_result_test[Index],Expected_result_test[Ref],_parent)),"",LOOKUPVALUE(Expected_result_test[Index],Expected_result_test[Ref],_parent))

VAR _path=

IF(

ISBLANK(_l1),

CONCATENATE(_index,CONCATENATE("|",Expected_result_test[Index])),

_l1

)

RETURN

_path

Re: PATH function extract sub-level 2 hierarchy

wdx223_Daniel — Thu, 21 Jan 2021 00:29:51 GMT

witbi here is the formula for your reference

you just need to change the number which the arrow points.

Re: PATH function extract sub-level 2 hierarchy

witbi — Thu, 21 Jan 2021 23:20:42 GMT

Wow, many thanks again wdx223_Daniel. This works! I have learned much from your clear and elegant solutions.