Forum Discussion

alexkelly101757's avatar
alexkelly101757
Frequent Visitor
7 years ago

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 

Show and Tell

6 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      alexkelly101757
      Frequent Visitor
      7 years ago

      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's avatar
    alexkelly101757
    Frequent Visitor
    7 years ago

    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's avatar
      v-frfei-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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's avatar
        alexkelly101757
        Frequent Visitor
        7 years ago

        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