Dax function to concatenate records from multiple rows

[hassanh2]

Hello,

can someone help please with the following!

I have the first 2 columns generated from a datasource, im trying to create a DAX function to create the last 3 columns. Basically for each product there can be one more colors, so 3rd column should concatenate the colors for the same (unique) products, then 4th coloum to flag the last record for each product and last column to display the count of colors in the concatenated column.

[FreemanZ · Accepted solution]

hi @hassanh2 ,

try like:

1) add an index column in Power Query or datasource

2) add the expected calculated columns like:

ColorCon = 
CONCATENATEX(
    CALCULATETABLE(
        DISTINCT(data[Color]),
        ALLEXCEPT(data, data[Product]),
        data[Index]<=EARLIER(data[Index])
    ),
    data[Color],
    "^"
)

Flag = 
VAR _max =
MAXX(
    FILTER(data, 
        data[Product]=EARLIER(data[Product])
    ),
    data[index]
)
VAR result = IF(data[index]=_max, "Last")
RETURN result

ColorCount = 
CALCULATE(
    DISTINCTCOUNT(data[Color]),
    ALLEXCEPT(data, data[Product]),
    data[Index]<=EARLIER(data[Index])
)

it worked like:

[hassanh2]

Thank you very much. This is very helpful.