Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
witbi
Helper I
Helper I

Extract parent for child based on ordered list reference

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

RefDescriptionEvent
1AndyA
1.1GreenA
1.2BlueA
1.2.1WaterA
2BillA
2.1YellowA
2.1.1WoodA
2.1.2PaperA
2.2OrangeA
3ChrisB
3.1PurpleB
4DaveB
4.1BlackB
4.2WhiteB
1AndyC
1.2BlueC
1.2.1WaterC
3ChrisC
3.1PurpleC

 

Target flat structure

Ordered ListNameEventLevel 1Level 2Level 3
1AndyAAndy  
1.1GreenAAndyGreen 
1.2BlueAAndyBlue 
1.2.1WaterAAndyBlueWater
2BillABill  
2.1YellowABillYellow 
2.1.1WoodABillYellowWood
2.1.2PaperABillYellowPaper
2.2OrangeABillOrange 
3ChrisBChris  
3.1PurpleBChrisPurple 
4DaveBDave  
4.1BlackBDaveBlack 
4.2WhiteBDaveWhite 
1AndyCAndy  
1.2BlueCAndyBlue 
1.2.1WaterCAndyBlueWater
3ChrisCChris  
3.1PurpleCChrisPurple 
1 ACCEPTED SOLUTION

@witbi here is the file.

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hello @witbi 
Please refer to the link .

 

wdx223_Daniel
Super User
Super User

@witbi 

wdx223_Daniel_0-1611026465377.png

you need a dim table with no relationship

wdx223_Daniel_1-1611026499891.png

 

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

 

 

@witbi here is the file.

 

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
Super User
Super User

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

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors