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])

[siddhartha0208]

Hi Amit, Nice solution. I've another challenge. Data Looks like the following.

NewsID	Parent	Child
N1	A	A1
N1	A	A2
N1	A	A3
N1	B	B1
N1	B	B2
N2	C	C1
N2	C	C2
N3	C	C3
N3	C	C4
N2	D	D1
N3	D	D2
N3	D	D3

Now my output is being presented into a table visual - wherein for each newsID N1, N2 and N3 I've 1 row to display. Against each NewsID - I need to display the Parent and their child IDs concatenated like:

News	Linked Parent_Child
N1	A(A1, A2,A3); B(B1,B2)
N2	C(C1, C2); D(D1)
N3	C(C3, C4); D(D2,D3)

[pisca]

Awesome, Thanks a lot..