Struggling to figure out how to work with parent/child data - how can I filter by parent?

[Anonymous]

I have a dataset that includes a table called "Spaces". That table includes spaces with "SpaceIDs" and also "ChildrenIDs" as another column for spaces with Children IDs. Children IDs are also SpaceIDs. 

I broke it up into two tables and created a "SpaceRelationship" table that maps SpaceIDs to ChildrenIDs. I also added a Path column for the hirarchy. I am trying to figure out what's next and how I can actually use this to say, return all the data from the children IDs of a space. 

The data is structured like this example: 

-A SpaceID for a building may have 5 ChildrenIDs representing each five floors, these ChildrenIDs are also SpaceIDs

-Each of the 5 ChildrenIDs representing each floor may have 1-500 or more ChildrenIDs that represent different rooms on the floor

My goal is to answer questions like: 

-How many spaces are there in a given building, what are the floors, what are the spaces under each floor

-I have other tables that connect on SpaceID, such as space occupancy, I want to be able to see the occupancy level for a building, and then drill down and see it by floor or room

Any ideas for how to set this up and actually make use/sense of the parent/child relationship? 

[v-chenwuz-msft]

Hi @Anonymous ,

Please use PATH(<ID_columnName>, <parent_columnName>) to return a path and use PATHLENGTH(<path>) to return the level, the level is a number. For example, a is the parents level and aa is the children1 level and aaa is children2 level which parent is aa. And so on. So, the PATHLENGTH(<path>) will return 3 for aaa.

For more details, you can refer this:

Understanding functions for parent-child hierarchies in DAX - DAX | Microsoft Learn

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Once I have the PATHLENGTH column, how do I use it to sort/drill with the data?