Forum Discussion

pstanek's avatar
pstanek
Post Patron
9 years ago

Self relation

 

 

Hi I have  table with columns parent id  and id

 

I need to create calculated column  Nazev 2 , where if parent id is not empty, it would find value from fourth column Nazev by finding parent id value  in id column.

 

For example

id    Nazev2

234  Aktivní život

231 Ostatni

235 Žena a muž

 

It has to work in direct query.

 

 

Need Help

8 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    pstanek


    You can track the parent name according to parent_id using PATH and PATHITE function, then use LOOKUPVALUE function to lookup the name. Finally, create a table including the id and name columns.

    I try to reproduce your scenario using your given sample data, use simple A,B, C….. as name assumption, and get expected result. More details will be shown as follows.

     

    First, create a calculated column using the formula: Parent_name = IF(ISBLANK(Table7[parrent_id]),"NULL",LOOKUPVALUE(Table7[name],Table7[id], PATHITEM(Table7[Column],1)))

    It will return NULL if the parent_id is null. Otherwise, it will returns the parent name.




    Then, create a table, select the id and Parent_name as Values. Like the highlighted in red line, filter the result using Parent_name which does not contain null values.

     

    If you have any question, please let me know, thank you very much.

    Best Regards,
    Angelia

    • pstanek's avatar
      pstanek
      Post Patron
      9 years ago

      But It say´s   that My power bi doesn´t recognise vlookup funcion. Is it working in direct query.

      I think I is calculcated column in Query Editor. Is there difference between calculated column in power bi -- similar to measure and calculated column in query editor within power bi?