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

iplaygod

 

 

You can store the value of a MEASURE in a variable and then convert it into a list

For example

Suppose you have a MEASURE

Measure = "1,2,3,5"



You can use it in another measure like this

 

Measure 2 = VAR mymeasure=SUBSTITUTE([Measure],",","")
VAR Mylen=len(mymeasure)
VAR mytable=ADDCOLUMNS(GENERATESERIES(1,mylen),"mylist",VALUE(Mid(mymeasure,[Value],1)))
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!