Forum Discussion

hassanh2's avatar
hassanh2
Icon for Helper I rankHelper 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 pro...
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:

     

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper 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:

 

hassanh2's avatar
hassanh2
Icon for Helper I rankHelper I
2 years ago

Thank you very much. This is very helpful.