topic Re: Extract parent for child based on ordered list reference in DAX Commands and Tips

Extract parent for child based on ordered list reference

witbi — Tue, 19 Jan 2021 02:30:33 GMT

Hello everyone,

I have a table that has an ordered list reference which I would like to convert into a flat hierarchy to use in filters. However, I'm stuck on how to extract the parent for each child. If anyone has ideas on how this could be easily done in DAX that would be most helpful.

Many thanks

Example of the starting table

Ref	Description	Event
1	Andy	A
1.1	Green	A
1.2	Blue	A
1.2.1	Water	A
2	Bill	A
2.1	Yellow	A
2.1.1	Wood	A
2.1.2	Paper	A
2.2	Orange	A
3	Chris	B
3.1	Purple	B
4	Dave	B
4.1	Black	B
4.2	White	B
1	Andy	C
1.2	Blue	C
1.2.1	Water	C
3	Chris	C
3.1	Purple	C

Target flat structure

Ordered List	Name	Event	Level 1	Level 2	Level 3
1	Andy	A	Andy
1.1	Green	A	Andy	Green
1.2	Blue	A	Andy	Blue
1.2.1	Water	A	Andy	Blue	Water
2	Bill	A	Bill
2.1	Yellow	A	Bill	Yellow
2.1.1	Wood	A	Bill	Yellow	Wood
2.1.2	Paper	A	Bill	Yellow	Paper
2.2	Orange	A	Bill	Orange
3	Chris	B	Chris
3.1	Purple	B	Chris	Purple
4	Dave	B	Dave
4.1	Black	B	Dave	Black
4.2	White	B	Dave	White
1	Andy	C	Andy
1.2	Blue	C	Andy	Blue
1.2.1	Water	C	Andy	Blue	Water
3	Chris	C	Chris
3.1	Purple	C	Chris	Purple

Re: Extract parent for child based on ordered list reference

lbendlin — Tue, 19 Jan 2021 02:52:25 GMT

Create your lists in Power Query, using pipe "|" as the concatenator. Then in DAX you can use PATHITEM etc functions.

Re: Extract parent for child based on ordered list reference

wdx223_Daniel — Tue, 19 Jan 2021 03:21:54 GMT

witbi

you need a dim table with no relationship

Re: Extract parent for child based on ordered list reference

Anonymous — Tue, 19 Jan 2021 04:53:29 GMT

Hello witbi
Please refer to the link .

Re: Extract parent for child based on ordered list reference

witbi — Tue, 19 Jan 2021 14:16:12 GMT

Thanks wdx223_Daniel,
This looks to be an elegant solution but I can't seem to get the syntax right? Would you possibly be able to upload the sample?

Name =

VAR _m=MAX('DimLevels'[Value])

VAR _p=SUBSTITUTE(MAX('Table'[Ref]),".","|")

VAR _e=MAX('Table'[Event])

VAR _ref=CONCATENATEX(FILTER(ALL('DimLevels'),'DimLevels'[Value]<= _m), PATHITEM(_p,'DimLevels'[Value]),".")

RETURN

IF(_m <= PATHLENGTH(_p),LOOKUPVALUE('Table'[Description],'Table'[Ref],_ref,'Table'[Event],_e),)

Re: Extract parent for child based on ordered list reference

wdx223_Daniel — Wed, 20 Jan 2021 00:35:14 GMT

witbi here is the file.

Re: Extract parent for child based on ordered list reference

witbi — Wed, 20 Jan 2021 18:37:44 GMT

Many thanks wdx223_Daniel. Your approach using a measure is a very nice solution.

However, I'm looking for a calculated column so I can generate the separate columns for each level to use in a slicer.

I opened another question PATH function extract sub-level 2 hierarchy, where I am trying to generate a path hierarchy based on an introduced index. However I'm stuck on that. If you have ideas please do let me know!

Re: Extract parent for child based on ordered list reference

witbi — Wed, 20 Jan 2021 18:41:16 GMT

Thanks @lbendlin. I have opened another question PATH function extract sub-level 2 hierarchy to look at this approach. wdx223_Daniel 's measure solution may well help others so I've accepted his solution and will progress your suggested approach on the other question.