Concatenate via a calculated column formula

[Ashish_Mathur]

Hi,

In the image below, the first 2 columns are the input columns and the third one is the expected result.  In each row of the table, for the fruit in the current row, i wish to concatenate all the unique calorie values via a calculated column formula.

Fruit	Calorie	Expected result
Apple	High	High, Low
Pear	Medium	Medium, High
Banana	High	High, Medium
Apple	Low	High, Low
Banana	Medium	High, Medium
Pear	High	Medium, High
Apple	High	High, Low

Thank you for your help.

[DataInsights · Accepted solution]

@Ashish_Mathur,

Try this calculated column:

Unique Calorie =
VAR vFruit = Table1[Fruit]
VAR vTable =
    CALCULATETABLE (
        VALUES ( Table1[Calorie] ),
        Table1[Fruit] = vFruit,
        ALL ( Table1 )
    )
VAR vResult =
    CONCATENATEX ( vTable, Table1[Calorie], ", " )
RETURN
    vResult

[Ashish_Mathur]

Hi,

Thank you for your help.  Your formula worked very well.