Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Concatenate text by group : EARLIER function not working

Hello everyone,

 

I am trying to create a measure in DAX to concatenate all CODES that have the same Group ID.

I have tried the CONCATENATEX function mentioned in many other discussion threads but it doesn't seem to function as there appears to be a problem with the function EARLIER which doesn't recognize any value.

This is the initial table:

Group Registration IDSPN CODE
578-24-3545895SM-CC
578-24-3545895WR-SF
578-24-3544933FU-TR
578-24-3544933SM-AD
578-24-3544933SM-CC
578-24-3544933WR-SF
578-24-3532081WR-SF
578-24-3524650SM-DP
578-24-3518403LP-MS
578-24-3518403N/A

The desired output should be as such:

578-24-3545895SM-CCSM-CC; WR-SF
578-24-3544933FU-TRFU-TR; SM-AD; SM-CC; WR-SF
578-24-3532081WR-SFWR-SF
578-24-3524650SM-DPSM-DP
578-24-3518403LP-MSLP-MS; N/A

I have tried this but it's not working and I have the following error :

CODES = CONCATENATEX(FILTER(SPN, SPN[Group Registration ID]=EARLIER(SPN[Group Registration ID])), SPN[SPN CODE], "; ")

 

I would appreciate any orientation

 

Thank you

CONCATENATEX
Earlier Function
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,
    JamesFR06 Good solution.
    Here's what I need to add
    According to mine using your data to test the dax you wrote in power bi, when I put your dax code into measure, he got the same error as you. But when you use the calculated columns, your dax code runs with the results you want. According to the documentation I checked, EARLIER succeeds if there is a row context prior to the beginning of the table scan. otherwise it returns an error.

    Also, you can create a new table

    Table = 
ADDCOLUMNS(
    SUMMARIZE('SPN', 'SPN'[Group Registration ID]),
    "Codes", 
    CONCATENATEX(
        FILTER('SPN', 'SPN'[Group Registration ID] = EARLIER('SPN'[Group Registration ID])),
        'SPN'[SPN CODE],
        "; "
    )
)

    Final output


    EARLIER function (DAX) - DAX | Microsoft Learn

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

4 Replies

  • JamesFR06's avatar
    JamesFR06
    Resolver IV
    2 years ago

    Please find the measure :

    Mesure 2 =
    var Groupe=SELECTEDVALUE(Concat[Group Registration ID])
    var result=CONCATENATEX(filter(Concat,Concat[Group Registration ID]=groupe),Concat[SPN CODE],",")
    return
    result

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,
    JamesFR06 Good solution.
    Here's what I need to add
    According to mine using your data to test the dax you wrote in power bi, when I put your dax code into measure, he got the same error as you. But when you use the calculated columns, your dax code runs with the results you want. According to the documentation I checked, EARLIER succeeds if there is a row context prior to the beginning of the table scan. otherwise it returns an error.

    Also, you can create a new table

    Table = 
ADDCOLUMNS(
    SUMMARIZE('SPN', 'SPN'[Group Registration ID]),
    "Codes", 
    CONCATENATEX(
        FILTER('SPN', 'SPN'[Group Registration ID] = EARLIER('SPN'[Group Registration ID])),
        'SPN'[SPN CODE],
        "; "
    )
)

    Final output


    EARLIER function (DAX) - DAX | Microsoft Learn

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      This is the simplest function that solvedit. Thank you.