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?
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!
Thanks it's great, I'll use it!
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?
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:
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.
I have created a Data Shuttle upload, with no filters, to replace rows on my Smartsheet destination sheet. I discovered that all rows were not being added to the destination sheet. Debugging narrowed the problem down to the contents of one column. In Smartsheet, the column is formatted as Text/Number. In Excel for testing,…
We are considering upgrading to Advance, and I have questions about Bridge capabilities: Our project blueprint includes multiple reports that pull entries from master logs based on the job number. This means that our admins have to add the job number to every single new report filter when they set up a project. Can Bridge…
Hi, Can dynamic views be accessed on smartphones/mobile devices? I don't see this option on the app.
Help shape the future of Smartsheet.
Share your ideas and feature requests.
©2023. All Rights Reserved Smartsheet Inc.