Forum Discussion
Building a hierarchy
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?
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... :)
4 Replies
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
AssetIDPreditably 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?
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.
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... :)
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. :)
