topic Concatenate text by group : EARLIER function not working in DAX Commands and Tips

Concatenate text by group : EARLIER function not working

Anonymous — Tue, 05 Mar 2024 12:13:57 GMT

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 ID	SPN CODE
578-24-3545895	SM-CC
578-24-3545895	WR-SF
578-24-3544933	FU-TR
578-24-3544933	SM-AD
578-24-3544933	SM-CC
578-24-3544933	WR-SF
578-24-3532081	WR-SF
578-24-3524650	SM-DP
578-24-3518403	LP-MS
578-24-3518403	N/A

The desired output should be as such:

578-24-3545895	SM-CC	SM-CC; WR-SF
578-24-3544933	FU-TR	FU-TR; SM-AD; SM-CC; WR-SF
578-24-3532081	WR-SF	WR-SF
578-24-3524650	SM-DP	SM-DP
578-24-3518403	LP-MS	LP-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

Re: Concatenate text by group : EARLIER function not working

HotChilli — Tue, 05 Mar 2024 16:08:41 GMT

If you are writing a measure, it will be a bit simpler than the code shown.

Follow the example here https://learn.microsoft.com/en-us/dax/concatenatex-function-dax?WT.mc_id=DP-MVP-4025372#example

Re: Concatenate text by group : EARLIER function not working

JamesFR06 — Tue, 05 Mar 2024 16:52:43 GMT

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

Re: Concatenate text by group : EARLIER function not working

Anonymous — Wed, 06 Mar 2024 06:16:37 GMT

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

Re: Concatenate text by group : EARLIER function not working

Anonymous — Wed, 06 Mar 2024 10:32:55 GMT

This is the simplest function that solvedit. Thank you.