Need Assistance with DAX

[InsightSeeker]

Hello – I need assistance to join all the From_Dates and against each unique value in the assending order (Leg_Num)

For each unique_id:

- Find the first Leg_Num and find the corresponding From_Date  and

- Find the second Leg_Num and find the corresponding From_Date  and

- Find the third Leg_Num and find the corresponding From_Date  and

- Find the fourth Leg_Num and find the corresponding From_Date and

Continue this process...

Use "|" between From_Date in the final result.

Data 

unique_id	Leg_Num	From_Date
10141372	1	10-Sep-24
10141372	2	11-Sep-24
10141372	3	9-Oct-24
10141372	4	9-Oct-24
10141376	1	19-Aug-24
10141376	2	22-Aug-24
10141438	1	24-Oct-24
10141438	2	24-Oct-24
10141438	3	21-Nov-24
10141438	4	21-Nov-24
10141456	1	13-Sep-24
10141456	2	24-Sep-24
10141456	3	24-Sep-24
10141465	1	14-Sep-24
10141465	2	14-Sep-24
10141465	3	24-Sep-24
10141465	4	24-Sep-24

Result

unique_id	Leg_Num	From_Date	Column2
10141372	1	10-Sep-24	10-Sep-24 | 11-Sep-24 | 09-Oct-24 | 09-Oct-24
10141372	2	11-Sep-24	10-Sep-24 | 11-Sep-24 | 09-Oct-24 | 09-Oct-24
10141372	3	09-Oct-24	10-Sep-24 | 11-Sep-24 | 09-Oct-24 | 09-Oct-24
10141372	4	09-Oct-24	10-Sep-24 | 11-Sep-24 | 09-Oct-24 | 09-Oct-24
10141376	1	19-Aug-24	19-Aug-24 | 22-Aug-24
10141376	2	22-Aug-24	19-Aug-24 | 22-Aug-24
10141438	1	24-Oct-24	24-Oct-24 | 24-Oct-24 | 21-Nov-24 | 21-Nov-24
10141438	2	24-Oct-24	24-Oct-24 | 24-Oct-24 | 21-Nov-24 | 21-Nov-24
10141438	3	21-Nov-24	24-Oct-24 | 24-Oct-24 | 21-Nov-24 | 21-Nov-24
10141438	4	21-Nov-24	24-Oct-24 | 24-Oct-24 | 21-Nov-24 | 21-Nov-24
10141456	1	13-Sep-24	13-Sep-24 | 24-Sep-24 | 24-Sep-24
10141456	2	24-Sep-24	13-Sep-24 | 24-Sep-24 | 24-Sep-24
10141456	3	24-Sep-24	13-Sep-24 | 24-Sep-24 | 24-Sep-24
10141465	1	14-Sep-24	14-Sep-24 | 14-Sep-24 | 24-Sep-24 | 24-Sep-24
10141465	2	14-Sep-24	14-Sep-24 | 14-Sep-24 | 24-Sep-24 | 24-Sep-24
10141465	3	24-Sep-24	14-Sep-24 | 14-Sep-24 | 24-Sep-24 | 24-Sep-24
10141465	4	24-Sep-24	14-Sep-24 | 14-Sep-24 | 24-Sep-24 | 24-Sep-24

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for adding a new column.

expected result CC =
CONCATENATEX (
    FILTER ( data, data[unique_id] = EARLIER ( data[unique_id] ) ),
    data[From_Date],
    " | ",
    data[Leg_Num]
)

[_AAndrade · Accepted solution]

Hi @InsightSeeker,

Please try this dax formula as a calculated column:

NewColumn = 
VAR CurrentID = 'f_Data'[unique_id]
RETURN
    CONCATENATEX(
        FILTER(
            'f_Data',
            'f_Data'[unique_id] = CurrentID
        ),
        FORMAT('f_Data'[From_Date], "dd-mmm-YY"),
        " | "
    )

The final output would be this:

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for adding a new column.

expected result CC =
CONCATENATEX (
    FILTER ( data, data[unique_id] = EARLIER ( data[unique_id] ) ),
    data[From_Date],
    " | ",
    data[Leg_Num]
)