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'[Dis...
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.

     

     

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.