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
Ok this seems like a very promising start!
Thanks so much for the suggestion!

But what happens when I cannot rely on the numbers being the same size in chars,

 

so lets say the string is:

 

"1,40,567,4,56708"

 

ie the numbers are all different lengths...

then what?

how do I iterate over that?

 

thanks again

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
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 )

 

 

  • 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