Forum Discussion

hassanh2's avatar
hassanh2
Helper I
2 years ago
Solved

Dax function to concatenate records from multiple rows

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.

 

 

Need Help
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    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:

     

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    2 years ago

    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: