Comma Separated to One Row using DAX

[Creative_tree88]

Hi all - I have some data (see sample attached), which has been derived using the following DAX:

Exam Range =

VAR _start = 'SLAM Data Nov 24'[Admission Date]

VAR _end = 'SLAM Data Nov 24'[Discharge Date]

VAR _key = 'SLAM Data Nov 24'[Best Hosp No]

VAR _examsInRange =

    FILTER(

        Activity,

        Activity[Event Date]>= _start &&

        Activity[Event Date] <= IF(ISBLANK(_end), _start, _end) &&

       Activity[Best Hosp No]= _key

    )

VAR _result =

            CONCATENATEX(

            _examsInRange,

               Activity[Examinations],

               ","

            )

RETURN

IF(

    ISBLANK(_result),

    BLANK(),

    _result)

This DAX looks up examinations from a table called 'Activity' and finds which one it is, between two dates.  Works really well.  However, some of the lines are pulling back multiple exams (which is correct) but rather than having them on one line, separated by commas, I'd like them on a separate row, identifed and by using a field called 'Report ID' which is a unique code for each exam / exam combination.

Is there a way to either modify this DAX, to accomplish this.  Or, is there a way to add another column and perform another DAX on this result, to produce the desired result?

I've linked to some sample data which hopefully shows you what I mean - the left set of data represents what I have, the right side shows what I'd like to have once the examinations are separated and put onto a separate row.

Many thanks!

Sample & Required 

[Bibiano_Geraldo · Accepted solution]

Hi @Creative_tree88 ,

You can achieve the desired result by creating a new calculated table using the following DAX:

NewTable = 
VAR Separator = "|"
RETURN
SELECTCOLUMNS(
    GENERATE(
        'YourOriginalTable',
        VAR ExaminationsList = SUBSTITUTE('YourOriginalTable'[Examinations], ",", Separator)
        RETURN 
        SELECTCOLUMNS(
            GENERATESERIES(1, LEN(ExaminationsList) - LEN(SUBSTITUTE(ExaminationsList, Separator, "")) + 1),
            "Report ID2", [Report ID],
            "SplitValue", PATHITEM(ExaminationsList, [Value], TEXT)
        )
    ),
    "Report ID2", [Report ID2],
    "SplitValue", [SplitValue]
)

Your output will look like this:

Make sure to replace table and columns names with your owns.

[Creative_tree88]

@Kedar_Pande  Thanks.  However, this needs to be done in DAX, not Power Query.  The column is a calculated column based on DAX query, so does not appear on Power Query.  Appreciate your reply though - would have been nice and simple if this was the case.

[Kedar_Pande]

@Creative_tree88 

1. In Power query, select the column containing the comma-separated exams.
2. Use the Split Column functionality to split the exams into separate rows. You can use the Split to Rows feature or perform a custom split by delimiter.
3. Add the Report ID column by referencing the unique code for each exam in the table.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn