How to get the first parent name even if parent doesnt exist

[alexsvi]

So I have something like this :

ID	ParentID	Name	Level
1		Name1	1
2	1	Name2	2
3	2	Name3	3
4	3	Name4	4
5	2	Name5	3
6	4	Name6	5
7	23	Name7	3

What I want to accompish is to have the first parent name on every column.

 The thing is that the parentID can be inexistant. 

So in my example every FirstParentName would be Name1 except for ID 7. (i dont care what the FirstParentName would be for ID 7. I just dont want PowerBI to give me errors).

Any idea how to create this new column?

Thanks a lot!

[mahoneypat · Accepted solution]

This requires the PATH function, but requires that all parents also exist in the ID column. To avoid this, you must first create another column to fix it.

Adj Parent á var thisparent á 'Parent'[ParentID]

var absent á ISBLANK(CALCULATE(COUNTROWS('Parent'), ALL('Parent'), 'Parent'[ID] á thisparent))

return if (absent,'Father'[ID], 'Father'[ParentID])

The above does a column that checks whether that parent is present; if it is not, it uses the ID value (which is present, of course). You can then add this column expression to get the first item in the path for each identifier.

First Parent to PATHITEM(PATH('Parent'[ID], 'Parent'[Adj Parent]), 1)

You can add additional logic if the first parent equals the ID and then returns BLANK().

For your information I wrote a blog on this topic in 2017 - https://powerpivotpro.com/2017/12/imagine-people-tables/

Best regards

Pat

[ChrisMendoza]

@alexsvi  -

Are you attempting to use PATH ( ) simliar to https://www.daxpatterns.com/parent-child-hierarchies/# and you're receiving '23 does not exist'?

Does using the below satisfy your need?

EntityPath =
IFERROR (
    LOOKUPVALUE ( 'Table'[Name], 'Table'[ID], 'Table'[ParentID] ),
    BLANK ()
)