Forum Discussion

iplaygod's avatar
iplaygod
Resolver I
7 years ago
Solved

DAX how split a string by delimiter into a list or array?

NOTE: I am not trying to write calculated columns. I am writing dax inside measures. I am trying to use the result of one measure as the filter for another measure. This is to avoid having to re-...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    iplaygod

     

    I think,,,,In that case we can use this MEASURE :smileywink:

     

     

     

    Measure 3 =
VAR mymeasure =
    SUBSTITUTE ( [Measure], ",", "|" )
VAR Mylen =
    LEN ( mymeasure )
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, mylen ),
        "mylist", VALUE ( PATHITEM ( mymeasure, [Value] ) )
    )
VAR mylist =
    SELECTCOLUMNS ( mytable, "list", [mylist] )
RETURN
    CALCULATE ( COUNTROWS ( Table1 ), Table1[ID] IN mylist )

     

     

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    TomMartensiplaygod

    Ohh. Yes

     

    This was exactly in my mind. I just forgot to replace LEN when I copy pasted myfirst formula.

     

    Measure 3 =
VAR mymeasure =
    SUBSTITUTE ( [Measure], ",", "|" )
VAR Mylen =
    PATHLENGTH ( mymeasure )
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, mylen ),
        "mylist", VALUE ( PATHITEM ( mymeasure, [Value] ) )
    )
VAR mylist =
    SELECTCOLUMNS ( mytable, "list", [mylist] )
RETURN
    CALCULATE ( COUNTROWS ( Table1 ), Table1[ID] IN mylist )
iplaygod's avatar
iplaygod
Resolver I
7 years ago

Zubair_Muhammad
Wow :)

ok you introduced me to many new DAX functions, I love it! ;)

I will try the measure you have suggested here. will get back!

iplaygod's avatar
iplaygod
Resolver I
7 years ago

Ok Zubair_Muhammad so I tried your latest suggested measure

and it ALMOST works.

however there is a bug:
Length of the string is still used to decide the nr of elements generated in the list
this worked when each number was 1 char long, but now all bets are off.
how do we know number of items in the string after splitting it, so that the list generates the correct amount of items?
thanks again

 

Measure 3 =
VAR mymeasure =
    SUBSTITUTE ( [Measure], ",", "|" )


// can we get correct nr of items inside the delimited string some other way?
VAR Mylen =
    LEN ( mymeasure )  //this probably doesnt work
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, mylen ),
        "mylist", VALUE ( PATHITEM ( mymeasure, [Value] ) )
    )
VAR mylist =
    SELECTCOLUMNS ( mytable, "list", [mylist] )
RETURN
    CALCULATE ( COUNTROWS ( Table1 ), Table1[ID] IN mylist )


 

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    Hey,

     

    iplaygod, Zubair_Muhammad

     

    guess this one will help to get everything working again just replace this 

     

    VAR Mylen =
    LEN ( mymeasure )  //this probably doesnt work

     with this

     

    VAR Mylen =
    PATHLENGTH ( mymeasure )

    As soon as a separator is replaced with the pipe sign "|" the string now represents a path and the PATH... functions can be used, e.g. PATHLENGTH and PATHITEM

     

    Regards,

    Tom

     

     

    • iplaygod's avatar
      iplaygod
      Resolver I
      7 years ago

      Zubair_MuhammadTomMartens

       

      thank you both so much!
      i will come back and paste in the final code that I ended up with when i have it ready soon

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

        TomMartensiplaygod

        Ohh. Yes

         

        This was exactly in my mind. I just forgot to replace LEN when I copy pasted myfirst formula.

         

        Measure 3 =
VAR mymeasure =
    SUBSTITUTE ( [Measure], ",", "|" )
VAR Mylen =
    PATHLENGTH ( mymeasure )
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, mylen ),
        "mylist", VALUE ( PATHITEM ( mymeasure, [Value] ) )
    )
VAR mylist =
    SELECTCOLUMNS ( mytable, "list", [mylist] )
RETURN
    CALCULATE ( COUNTROWS ( Table1 ), Table1[ID] IN mylist )