Forum Discussion
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 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 SupportHi 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
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])ThanksBrilliant, 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