Building a hierarchy

[cats_five]

I am trying to construct a visualisation with drill-down on data from an SQL Server database.  This is an overview of my tables:

COUNTY      TOWN         AREA        ASSET          DEFECT
CountyID    TownID       AreaID      AssetID        DefectID
CountyName  TownName     AreaNam     AssetName      DefectCategoryID
            CountyID     TownID      AreaID         DefectCauseID
                                                    AssetID

Preditably there are many towns in a county, many areas in a town and many assets in an area, and one asset can have many defects.  I'd like to be able to start with (e.g.) defects by category by County, and to be able to drill down to Towns, then Areas.

 The relationships between the tables in Power BI are correct.

It looks to me like the PowerBI Hierarchy needs all the ID fields in the Asset table?  If it does, how do I create the hierarchy?

[Eric_Zhang]

---

@cats_five wrote:

I am trying to construct a visualisation with drill-down on data from an SQL Server database.  This is an overview of my tables:

COUNTY      TOWN         AREA        ASSET          DEFECT
CountyID    TownID       AreaID      AssetID        DefectID
CountyName  TownName     AreaNam     AssetName      DefectCategoryID
            CountyID     TownID      AreaID         DefectCauseID
                                                    AssetID

Preditably there are many towns in a county, many areas in a town and many assets in an area, and one asset can have many defects.  I'd like to be able to start with (e.g.) defects by category by County, and to be able to drill down to Towns, then Areas.

 The relationships between the tables in Power BI are correct.

It looks to me like the PowerBI Hierarchy needs all the ID fields in the Asset table?  If it does, how do I create the hierarchy?

---

@cats_five

As the tables are in correct relationship and I don't think it needs all IDs in the asset table. Based on my test, you could put the countyname, townname,areaname from different tables and the drilling down works.

[cats_five · Accepted solution]

Thanks for your help.  Maybe I'm not clever enough with Power BI at present.

I ended up writing a custom SQL which selected all the columns I needed along with renaming columns (I don't want two called CategoryName or any number called ID!) and using that to pull in the data rather than letting Power BI do it itself.

I can now drill down to the children of where I click so I'm happy.  I'm also happy as it's probably pulling in less data (there are quite a few columns that are not needed), plus I understand SQL queries and it was easy to look at the results in SSMS to verify it's working correctly.  I do have an SQL issue but I'm sure it can be resolved - I've used Informix for years and SQL Server is just slightly different...  🙂

[Eric_Zhang]

@cats_five

Great to hear that, you could accept your reply as solution to close this thread. If you have any question, just feel free to post. 🙂

[cats_five]

To explain a bit further, I found I can drill down without a hierarchy but only to all values in the level below, rather than to just the ones below the area or town I click on.