Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
How to calculate parent from this SQL table? I need to create Parent-Child hierarchy somehow.
Solved! Go to Solution.
Hi @MattAllington I just happend to see your name when I was browsing the list of questions to find the one that one of our customers has posted and got curious. @HenrB I don't know if this is what you need but this calculated column should find the parent code of a given line in the table you use as an example.
= CALCULATE ( LASTNONBLANK ( VALUES ( 'table'[code] ), 1 ), FILTER ( ALL ( 'table'[code], 'table'[indentation] ), 'table'[code] < VALUE ( EARLIER ( 'table'[code] ) ) && 'table'[indentation] = VALUE ( EARLIER ( 'table'[indentation] ) ) - 1 ), ALL ( 'table' ) )
I take it that the 'code' field is a string field hence the 'VALUE()' function. Is it a NAV table I think I can recognize the structure?
Merry Christmas to both of you @MattAllington, @HenrB
Hi @Oxenskiold,
that's a very nice code!
Just in case there is the need to do this in the query editor, the corresponding M-code would look like this:
Table.AddColumn(PreviousStep, "Parent", (row) => List.Max( Table.SelectRows(PreviousStep, each [Code]< row[Code])[Code] and [Indentation]< row[Indentation] ) )
This is a Custom Column with some hand-edited code (in bold) for the nested "row-context".
It would enable you to break down your PC-hierarchy in an environment where you don't have to define the number of levels in advance and you could also use this simplified method for working with hierarchical schemas in PowerBI:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @MattAllington I just happend to see your name when I was browsing the list of questions to find the one that one of our customers has posted and got curious. @HenrB I don't know if this is what you need but this calculated column should find the parent code of a given line in the table you use as an example.
= CALCULATE ( LASTNONBLANK ( VALUES ( 'table'[code] ), 1 ), FILTER ( ALL ( 'table'[code], 'table'[indentation] ), 'table'[code] < VALUE ( EARLIER ( 'table'[code] ) ) && 'table'[indentation] = VALUE ( EARLIER ( 'table'[indentation] ) ) - 1 ), ALL ( 'table' ) )
I take it that the 'code' field is a string field hence the 'VALUE()' function. Is it a NAV table I think I can recognize the structure?
Merry Christmas to both of you @MattAllington, @HenrB
Hi @Oxenskiold,
that's a very nice code!
Just in case there is the need to do this in the query editor, the corresponding M-code would look like this:
Table.AddColumn(PreviousStep, "Parent", (row) => List.Max( Table.SelectRows(PreviousStep, each [Code]< row[Code])[Code] and [Indentation]< row[Indentation] ) )
This is a Custom Column with some hand-edited code (in bold) for the nested "row-context".
It would enable you to break down your PC-hierarchy in an environment where you don't have to define the number of levels in advance and you could also use this simplified method for working with hierarchical schemas in PowerBI:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks Matt, but I have only Code and Indentation, but not Parent. What is best way to calculate Parent. After that can use for example PATH function.
Create a new lookup table that does have what you want and the join it to the table you have, or replace the table you have
I know that I have to make some lookups or something, but how?
Well it depends. You could create a table that just has the parent row in "Enter Data" in Power BI, then append it to the table you have.
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |