How to find the top level parent in DAX?

[Anonymous]

I have a list of rooms in facilities, eg:

FacilityID	Facility Name	partOf
1	Health Centre	0
2	Office	1
3	Xray Department	1
4	XRay Room 1	3
5	XRay Room 2	3
6	Pharmacy	0

Facilities without a parent will have the parent ID of 0. I need to find the top level parent of each facility using DAX and save it as a seperate column called ParentFacility, to yield a calculated column as follows;

FacilityID	Facility Name	partOf	Parent
1	Health Centre	0	1
2	Office	1	1
3	Xray Department	1	1
4	XRay Room 1	3	1
5	XRay Room 2	3	1
6	Pharmacy	0	6

Thanks in advance. I can only figure out how to do this with nested if statements but there's no max level, so any help would be hugely appreciated.

Thank you!

[Anonymous]

That works perfectly and I learned something about heirarchies. Thank you so much!

[OwenAuger · Accepted solution]

@Anonymous 

You can make use of DAX PATH functions for this.

You will need to adjust the partOf column to convert zero values to blanks however.

1. Create a calculated column partOf Adjusted that converts zero to blank:

partOf Adjusted = 
IF ( Facility[partOf] <> 0, Facility[partOf] )

2. Created the calculated column Parent:

Parent = 
PATHITEM (
    PATH ( Facility[FacilityID], Facility[partOf Adjusted] ),
    1
)

The PATH function recursively constructs the path from topmost parent to a given node, and PATHITEM is used to extract an item in a particular position, in this case position 1.

Regards,

Owen