cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
HenrB Frequent Visitor
Frequent Visitor

Parent missing

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

 

data.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Oxenskiold Frequent Visitor
Frequent Visitor

Re: Parent missing

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

Super User
Super User

Re: Parent missing

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

7 REPLIES 7
Super User
Super User

Re: Parent missing

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
HenrB Frequent Visitor
Frequent Visitor

Re: Parent missing

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

 

 

 

 

 

Super User
Super User

Re: Parent missing

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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
HenrB Frequent Visitor
Frequent Visitor

Re: Parent missing

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

Super User
Super User

Re: Parent missing

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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Oxenskiold Frequent Visitor
Frequent Visitor

Re: Parent missing

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

Super User
Super User

Re: Parent missing

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 395 members 3,961 guests
Please welcome our newest community members: