Concatenate Parent and Child Value

[pisca]

Hi, 

How to create New Column like this table?

topic_id	topic_pid	topic	New Column
1	0	Hardware	Hardware
2	1	Computer	Hardware / Computer
3	1	Printer	Hardware / Printer
4	0	Office365	Office365
5	2	Word	Office365 / Word
6	2	Excel	Office365 / Excel
7	2	Power Point	Office365 / Power Point

[amitchandak · Accepted solution]

@pisca , Try a new column like

New column =
var _max = maxx(filter(Table, Table[topic_pid]=0 && [topic_id] <earlier([topic_id])),[topic_id])
return
if([topic_pid]<>0 , maxx(filter(Table, Table[topic_pid]=0 && [topic_id] =_max),[topic]) &"/" & [topic],[topic])

[Mariusz · Accepted solution]

Hi @pisca 

You can use this DAX column pattern for the below result.

New Column = 
VAR __getParentChildHierarchy = PATH( 'Table'[topic_id], 'Table'[topic_pid] )
VAR __selectFirstParent = PATHITEM( __getParentChildHierarchy, 1, INTEGER ) 
VAR __lookupTopicByID = 
    LOOKUPVALUE( 
        'Table'[topic], 
        'Table'[topic_id], __selectFirstParent 
    )
VAR __topic = 'Table'[topic]
RETURN 
IF( 
    NOT __lookupTopicByID == __topic, 
    __lookupTopicByID & " / " & __topic,
    __topic
)

This is if the topic_pid for office = 4, not 2 and you replace 0 with null so PATH() function works correctly

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[pisca]

Awesome, Thanks a lot..

[amitchandak · Accepted solution]

@pisca , Try a new column like

New column =
var _max = maxx(filter(Table, Table[topic_pid]=0 && [topic_id] <earlier([topic_id])),[topic_id])
return
if([topic_pid]<>0 , maxx(filter(Table, Table[topic_pid]=0 && [topic_id] =_max),[topic]) &"/" & [topic],[topic])

[pisca]

Awesome, Thanks a lot..