Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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?
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!
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.