creating higher hierarchy from a single table

[alexkelly101757]

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 

[alexkelly101757]

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? 

[v-frfei-msft]

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

[alexkelly101757]

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 

Thanks 

[alexkelly101757]

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

[v-frfei-msft]

Hi @alexkelly101757,

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

For more details, please check the pbix as attached.

Regards,

Frank

[alexkelly101757]

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