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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HenrB
Regular Visitor

Parent missing

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

 

data.PNG

2 ACCEPTED SOLUTIONS
Oxenskiold
Advocate I
Advocate I

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

 

View solution in original post

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...

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

View solution in original post

7 REPLIES 7
Oxenskiold
Advocate I
Advocate I

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:

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

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

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

 

data2.PNG

 

 

 

 

 

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.