topic Dax function to concatenate records from multiple rows in DAX Commands and Tips

Dax function to concatenate records from multiple rows

hassanh2 — Mon, 30 Oct 2023 09:37:00 GMT

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.

Re: Dax function to concatenate records from multiple rows

FreemanZ — Tue, 31 Oct 2023 02:14:33 GMT

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:

Re: Dax function to concatenate records from multiple rows

hassanh2 — Thu, 02 Nov 2023 10:40:53 GMT

Thank you very much. This is very helpful.