Forum Discussion
Helper IExtract 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
| 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 |
witbi here is the file.
7 Replies
- lbendlin
Super User
Create your lists in Power Query, using pipe "|" as the concatenator. Then in DAX you can use PATHITEM etc functions.
witbiThanks @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.
- wdx223_Daniel
Community Champion
- 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]),".")RETURNIF(_m <= PATHLENGTH(_p),LOOKUPVALUE('Table'[Description],'Table'[Ref],_ref,'Table'[Event],_e),)- wdx223_Daniel
witbi here is the file.
