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

creating higher hierarchy from a single table

Hello,

 

I want to create a hierarchy from a single table.  The table is made up of assets, an asset can be a site, location and sub location, or a asset and the structure is in that order too.  So "site" is the top level and "sub location" is at the bottom of the tree.  A assett can hang off of any of those locations.  How can I create that structure within Power BI? 

 

Thanks 

6 REPLIES 6
Community Support Team
Community Support Team

Re: creating higher hierarchy from a single table

Hi @alexkelly101757,

 

One sample for your reference. I create a hierarchy  column by just add the columns to assets one by one.

 

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,

Frank

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
alexkelly101757 Frequent Visitor
Frequent Visitor

Re: creating higher hierarchy from a single table

Thanks, I've replicated what you've done on my data set with the ID's.  However, my dataset is a bit more complicated than I first explained.  The dataset is tide together by ID numbers, those ID's numbers don't mean anything to the users of the system and are the ones viewing the reports too.  Here's the keys table columns : 

 

AssettID, SiteID, LocationID, SubLocationID, Description.   

 

Everyone knows the asset by the description, the ID's are never shown in the software front end and don't mean anything to the people using the software.  However, they are used to link the sites, locations to sub locations and the assets hanging off them.  I need to find a way to display description for each of the Assett, Site, Location, SubLocation.  How can i do this? 

 

Thanks 

alexkelly101757 Frequent Visitor
Frequent Visitor

creating higher hierarchy within a table

Hello,

 

I want to create a hierarchy from a single table.  The table is made up of assets, an asset can be a site, location and sub location, or a asset and the structure is in that order too.  So "site" is the top level and "sub location" is at the bottom of the tree.  A assett can hang off of any of those 3 levels (site, location and sub location).  How can I create that structure within Power BI? 

 

I've attached a copy of the dataset. 

 

 

Further information: The dataset is tide together by ID numbers, those ID's numbers don't mean anything to the users of the system and are the ones viewing the reports too.  Here's the keys table columns : 

 

AssettID, SiteID, LocationID, SubLocationID, DescriptionOfAsset.   

 

Everyone knows the asset by the description, the ID's are never shown in the software front end.  However, they are used to link the sites, locations to sub locations and the assets hanging off them.  I need to find a way to display description in the hiarchy for each of the Assett, Site, Location, SubLocation.  How can i do this? 

 

Community Support Team
Community Support Team

Re: creating higher hierarchy within a table

Hi @alexkelly101757 ,

 

How about using CONCATENATE to combine each of the levels with the description in the new columns and then just build the hierarchy manually in the visual by dragging these four new columns in to the axis portion of the chart?

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
alexkelly101757 Frequent Visitor
Frequent Visitor

Re: creating higher hierarchy within a table

That sounds like it could work but I'm not sure how to do it.  In order to do this I would need to create a lookup for the site (which is the Site ID) and search for that in assetID, then fetch the description and then create another function to concate that to the asset description against site id.  I'm falling over at the first hurdle in bringing back the site ID description.  The final parameter doesn't seam to allow a field id, it wants a string, iwant to look up from site.  Any help appreciated 

 

get site description  = LOOKUPVALUE(pmAsset[AssettDescription],pmAsset[AssetID],pmAsset[site])

get location description  = LOOKUPVALUE(pmAsset[AssettDescription],pmAsset[AssetID],pmAsset[location])
get sublocation description  = LOOKUPVALUE(pmAsset[AssettDescription],pmAsset[AssetID],pmAsset[sublocation])
 
Thanks
alexkelly101757 Frequent Visitor
Frequent Visitor

Re: creating higher hierarchy within a table

Brilliant, I sorted that last problem.  I created a calculated column instead of a column (Still getting my head around power bi), the query below is the one i used for anyone else reading: 

 

Assett_description  = CONCATENATE(pmAsset[AssetID], CONCATENATE(" ", LOOKUPVALUE(pmAsset[AssettDescription],pmAsset[AssetID],pmAsset[AssetID])))

 

The second problem is if I drill down, it shows blank IDs for assets that hang directly off the tree structure.  I've created a report to show all jobs for a given asset ID in matrix format. Assett 833 has 2157 jobs under the whole tree structure below it.  it also has 159 jobs directly attached but doesnt show a name.  this causes confusion.  How can i get power bi to display 833 name or somehow make it more apparent that those 159 jobs are directly attached to the 833?  This chart makes it pretty easy to understand and thats why ive attached it but the users want a pi chart and that makes things even more confusing for them.  See the pi chart picture below 

 

 

 

 

2019-02-25 11_38_32-Agility labour and asset reports - Power BI Desktop.png

 

 

 

2019-02-25 11_21_57-Agility labour and asset reports - Power BI Desktop.png

 

 

 

Thanks