How to create a Parent/Child hierarchy for accounts Id?

[bikelley]

Hello,

I need to create a parent and child hierarchy, but only some of the accounts have multiple branches. Basically, most of them don't have parents. Please see the below data. I am kinda stuck with logic, and getting some errors.

So my ID column has a bunch of IDs and the only way we know that Id is a child is the Preant_Id column has parent Id value.

Please advise me or show me how to do it. I need to get the name as well, but I think I do it to join the same table with parent ID.

Sample File - 

https://drive.google.com/file/d/1XQPJPx0CwovTg3T_N3WuO0SocEP-RXAG/view?usp=sharing

What I tried so far - 

Column = IF(Sample_dim[Parent_Id] <> "", Sample_dim[Parent_Id], Sample_dim[Id])
Path = PATH(Sample_dim[Id],Sample_dim[Parent_Id])

Sample Data

Thank you so much for the help 

[bikelley · Accepted solution]

I found a way to fix the error. 

First, you need to duplicate the table, remove all columns except parent Id. Then remove duplicated, rename it to ID  then append it to the main table. It will fix the issue. 

Basically, you going to insert parent ID into the id column, then remove dups. 

Thank you for all your help. 

[sevenhills]

To know about path and hierachy info,
https://docs.microsoft.com/en-us/dax/understanding-functions-for-parent-child-hierarchies-in-dax

https://simplebiinsights.com/power-bi-path-function-for-parent-child-hierarchies-in-dax/

During my quick review, I saw your data having gaps ... so I was not able to use Path  

[bikelley]

@sevenhills 

Hi, Thank you for taking look at my issue. Yes, I don't have a parent-child hierarchy for every single raw. I just want if they have parent id then create child hierarchy. I also tried PATH did not work for me.

Let me take a look at your other solution. Thank you so much

[sevenhills]

Parent Name =
VAR RowParent =
    SELECTEDVALUE ( Sample_dim[Parent_id] )
RETURN
    IF (
        ISBLANK ( RowParent ),
        "",
        LOOKUPVALUE ( Sample_dim[Name], Sample_dim[Id], RowParent )
    )

(as measure)

Parent Name =
VAR RowParent =
    Sample_dim[Parent_id] 
RETURN
    IF (
        ISBLANK ( RowParent ),
        "",
        LOOKUPVALUE ( Sample_dim[Name], Sample_dim[Id], RowParent )
    )

(as column)

[bikelley]

@sevenhills 

Hi, I try creating a measure and column and did not work for me. It is not showing me the child id if they have parent id (I mean it should show plus sing on parent id name then you will see child id name).

Am I doing something wrong? please correct me if I doing it wrong. 

Sample file with calculated column and measures

https://drive.google.com/file/d/1erJfJN4FDCgvxVkla_AmrSd4f2yzt0ZP/view?usp=sharing

Image

Thank you so much for your time and help. I really appreciate it.