Forum Discussion

Creative_tree88's avatar
Creative_tree88
Helper V
1 year ago
Solved

Comma Separated to One Row using DAX

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 

Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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.

     

     

3 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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

  • Creative_tree88's avatar
    Creative_tree88
    Helper V
    1 year ago

    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.

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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.