How to transfer information to power bi on a hierarchical level?
Hi,
I connected to smartsheet from powerbi,
I am trying to get information about a level in the hierarchy of each line i.e., is this a parent / child line
Is there a way to pass information to powerbi on a hierarchy level?
Thanks,
Rachel
Best Answer
-
Hi @Rachel r
I see that you're trying to identify levels of hierarchy, parent and child relationships from your sheet within Power BI. Happy to help with this.
There isn't current functionality to automatically identify hierarchical levels of parent or child, other than viewing the nesting within your sheet. Feel free to reach out to our Product Team to pass along this great suggestion by filling out your request here:
You could potentially achieve the desired effect however by creating a few additional helper columns that use hierarchical formulas to indicate nesting levels and parent rows. As an example of this you could create a column with Parent rows checked. Steps:
Create a Checkbox type column in your sheet titled "Check if Parent". Within row 1, use the formula, =IF(COUNT(CHILDREN()) > 0, 1, 0) and copy this down the column. If rows contain child rows beneath them, boxes will be checked indicating this:
You could also create another helper column to return the level of nesting for each row. Steps:
Create a Text Number type column in your sheet titled, "Nesting Level". Within row 1, use the formula, =COUNT(ANCESTORS()) and copy this down the column. This will return the hierarchical level for each row:
Additional guidance and information on using Hierarchy formulas can be found here:
Have a wonderful day!
Kind regards,
Eric
Answers
-
Hi @Rachel r
I see that you're trying to identify levels of hierarchy, parent and child relationships from your sheet within Power BI. Happy to help with this.
There isn't current functionality to automatically identify hierarchical levels of parent or child, other than viewing the nesting within your sheet. Feel free to reach out to our Product Team to pass along this great suggestion by filling out your request here:
You could potentially achieve the desired effect however by creating a few additional helper columns that use hierarchical formulas to indicate nesting levels and parent rows. As an example of this you could create a column with Parent rows checked. Steps:
Create a Checkbox type column in your sheet titled "Check if Parent". Within row 1, use the formula, =IF(COUNT(CHILDREN()) > 0, 1, 0) and copy this down the column. If rows contain child rows beneath them, boxes will be checked indicating this:
You could also create another helper column to return the level of nesting for each row. Steps:
Create a Text Number type column in your sheet titled, "Nesting Level". Within row 1, use the formula, =COUNT(ANCESTORS()) and copy this down the column. This will return the hierarchical level for each row:
Additional guidance and information on using Hierarchy formulas can be found here:
Have a wonderful day!
Kind regards,
Eric
-
Thanks it's great, I'll use it!
-
Hello,
I've added the formula to my sheet but cannot figure out how to show this in a hierarchical format within my Power BI report.
Please can you assist?
Regards,
-
I'm also still trying to figure this all out. I have found =PARENT([Row ID]@Row) works when you have a lot of different parents unrelated and with the auto number column as a Row ID(could use other columns to identify parents) this formula will identify the parent row for each child. (Power Bi doesn't like to sort numbers in order without preceding zeros) Maybe this information will help us get a step closer to solving this.
-
The two formulas:
- "=PARENT([Row ID]@Row)" and
- "Check if Parent". Within row 1, use the formula, =IF(COUNT(CHILDREN()) > 0, 1, 0) "
Could potentially work if it could show the row text instead of the check or row number. Power BI could then use the row text to filter based based on the hierarchy.
Right now in my PBI report I'm manually adding the parent row text to each row in a separate "helper coloum" to achieve this.
