Parent missing

[HenrB]

How to calculate parent from this SQL table? I need to create Parent-Child hierarchy somehow. 

[Oxenskiold · Accepted 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

[Oxenskiold · Accepted 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

[ImkeF · Accepted solution]

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:

http://www.thebiccountant.com/2016/07/21/easy-profit-loss-account-scheme-reports-power-bi-power-pivo...

[MattAllington]

Read this. http://www.daxpatterns.com/parent-child-hierarchies/

[HenrB]

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.

[MattAllington]

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

[HenrB]

I know that I have to make some lookups or something, but how?

[MattAllington]

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.