Forum Discussion

InsightSeeker's avatar
InsightSeeker
Icon for Helper III rankHelper III
2 years ago
Solved

Need Assistance with DAX

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_idLeg_NumFrom_DateColumn2
10141372110-Sep-2410-Sep-24 | 11-Sep-24 | 09-Oct-24 | 09-Oct-24
10141372211-Sep-2410-Sep-24 | 11-Sep-24 | 09-Oct-24 | 09-Oct-24
10141372309-Oct-2410-Sep-24 | 11-Sep-24 | 09-Oct-24 | 09-Oct-24
10141372409-Oct-2410-Sep-24 | 11-Sep-24 | 09-Oct-24 | 09-Oct-24
10141376119-Aug-2419-Aug-24 | 22-Aug-24
10141376222-Aug-2419-Aug-24 | 22-Aug-24
10141438124-Oct-2424-Oct-24 | 24-Oct-24 | 21-Nov-24 | 21-Nov-24
10141438224-Oct-2424-Oct-24 | 24-Oct-24 | 21-Nov-24 | 21-Nov-24
10141438321-Nov-2424-Oct-24 | 24-Oct-24 | 21-Nov-24 | 21-Nov-24
10141438421-Nov-2424-Oct-24 | 24-Oct-24 | 21-Nov-24 | 21-Nov-24
10141456113-Sep-2413-Sep-24 | 24-Sep-24 | 24-Sep-24
10141456224-Sep-2413-Sep-24 | 24-Sep-24 | 24-Sep-24
10141456324-Sep-2413-Sep-24 | 24-Sep-24 | 24-Sep-24
10141465114-Sep-2414-Sep-24 | 14-Sep-24 | 24-Sep-24 | 24-Sep-24
10141465214-Sep-2414-Sep-24 | 14-Sep-24 | 24-Sep-24 | 24-Sep-24
10141465324-Sep-2414-Sep-24 | 14-Sep-24 | 24-Sep-24 | 24-Sep-24
10141465424-Sep-2414-Sep-24 | 14-Sep-24 | 24-Sep-24 | 24-Sep-24
Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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's avatar
    _AAndrade
    2 years ago

    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:

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    _AAndrade
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    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: