Extract parent for child based on ordered list reference

[witbi]

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

[Anonymous]

Hello @witbi 
Please refer to the link .

[wdx223_Daniel]

@witbi 

you need a dim table with no relationship

[witbi]

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),)

[wdx223_Daniel · Accepted solution]

@witbi here is the file.

[witbi]

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!

[lbendlin]

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

[witbi]

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.