Forum Discussion

Creative_tree88's avatar
Creative_tree88
Helper V
1 year ago
Solved

Comma Separated Values

Hi all - I need to go from this: Report ID Examinations 57818 FCNRTJ 53863 ZANAE,IBIOPB,IBILXD,FPTCH 50320 FNVRK 31285 UFOOLJ 75614 UGMDCI 70578 UNECKN 47734 CLUN...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi Creative_tree88 

     

    You can make use of PATHITEM and then some crossjoins.

     

    -calculated table

DAXSplit =
VAR _Path =
    --add a pathitem column by substituting commas with pipes
    ADDCOLUMNS (
        'Table',
        "@PathItem", SUBSTITUTE ( 'Table'[Examinations], ",", "|" )
    )
VAR _MaxLength =
    --get the overall max path length
    MAXX (
        ADDCOLUMNS ( _Path, "@PathLength", PATHLENGTH ( [@PathItem] ) ),
        [@PathLength]
    )
VAR _Crossjoined =
    CROSSJOIN ( _Path, GENERATESERIES ( 1, _MaxLength, 1 ) )
VAR _ExamItem =
    FILTER (
        ADDCOLUMNS (
            _Crossjoined,
            "Exam Item", PATHITEM ( [@PathItem], [Value], TEXT )
        ),
        NOT ( ISBLANK ( [Exam Item] ) )
    )
RETURN
    SELECTCOLUMNS (
        _ExamItem,
        "Report ID", [Report ID],
        "Examinations", [Examinations],
        [Exam Item]
    )

    Please see the attached sample pbix.

     

Kedar_Pande's avatar
Kedar_Pande
Super User
1 year ago

Creative_tree88 

Create a new table:

Examinations_Split = 
VAR AddRows = 
ADDCOLUMNS (
'YourOriginalTable',
"Examination", 
PATHITEM('YourOriginalTable'[Examinations], 1)
)
RETURN
UNION(
SELECTCOLUMNS( AddRows, "Report ID", 'YourOriginalTable'[Report ID], "Examination", [Examination] ),
ADDCOLUMNS(
'YourOriginalTable',
"Examination", 
PATHITEM('YourOriginalTable'[Examinations], 2)
)
)

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