Dax help needed Again

[Lily_Lai346]

Hi There,

I would like to improvise on below formula, the desired result is to have below:

Contact id -  Program Name ( concatenated in a string by contact id, in ascending order of creation date of each record)

so meaning if there are two records for one contact id, LABC Program on creation date is 01/01/2022 & MBA Program on creation date 01/07/2022, then program name will be showing in a string like, "LABC,MBA" in a column.

Below is the formula that I have used but it does not take into consideration for each contact id record..

Programme of Interest by Creation Date = CONCATENATEX(FILTER(SUMMARIZE(MFSA,MFSA[Created Date/Time (FRA)],

MFSA[Program Name]),MFSA[Created Date/Time (FRA)]=EARLIER(MFSA[Created Date/Time (FRA)])),

MFSA[Program Name],",")

Any advice will be appreciated,

Thanks,

Lily

[Anonymous · Accepted solution]

Hi  @Lily_Lai346 ,

Here are the steps you can follow：

1. Create calculated column.

Test =
CONCATENATEX(
    FILTER(ALL('MFSA'),
    'MFSA'[Contact id]=EARLIER('MFSA'[Contact id])&&'MFSA'[Created Date/Time (FRA)]<=EARLIER('MFSA'[Created Date/Time (FRA)])),[Program Name],",")

2. Result:

If it doesn't meet your expected results, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

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

[amitchandak]

@Lily_Lai346 , Create a measure below and use with id column

Measure = CONCATENATEX(SUMMARIZE('MFSA',,MFSA[Created Date/Time (FRA)],MFSA[Program Name]),[Program Name],",",MFSA[Created Date/Time (FRA)])