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-write the filters inside the another measure again, and only define them in ONE measure.

I have a DAX measure that returns a string that has concatenated values.

like this

"1,2,3,4,5,6,7"

so far so good.

These are the values I want to get access to, but as a list, not as a string.
I want to split this string (by the delimiter ",") using DAX and convert it to a list of values instead.

 

After I have them as a list, I want to do a filter in a new measure

and do something like...

myTable[User ID] IN listOfValues

I have looked at the DAX text functions, but they only seem to join strings together, not split them apart (more than at 1 position).

is this possible to achieve? what is the function to use? how?

If its not possible, I have a second question:

Is it possible to have a measure that returns a list of values as a list, or returns a filtered column with multiple values in it?
Or must measures always return a single value?
im asking because then I could do something like

myTable[User ID] IN [Measure that returns a list of values]

Thanks alot in advance!

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 )

18 Replies

  • 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)

     

  • Oex's avatar
    Oex
    Advocate I
    6 years ago

    Further to this excellent approach, could you offer any advice about working with Text strings in the same context?

    It appears that using the same approach for using items that have been selected in a Slicer are not affecting the ALLSELECTED context?

    My example, based on an IMDB list of Movie titles

     

     

    Is Text Selected = 
VAR TextString =
    CONCATENATEX ( ALLSELECTED ( Title[types] ), Title[types], "|" )
VAR PathLen =
    PATHLENGTH ( TextString )
VAR VTable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, PathLen ),
        "SelectedItems", PATHITEM ( TextString, [Value] )
    )
VAR SelectedItems =
    SELECTCOLUMNS ( VTable, "ItemList", [SelectedItems] )
RETURN
    CALCULATE ( COUNTROWS ( Title ), Title[types] IN SelectedItems , VALUES(Title[types] ) )

     

     

    The base data shows 59 items at the top level for [type]

     

     

    Expectaction is that if the user Filters [types] from the hierarchy, the ALLSELECTED then subsequent VAR's should only pull through items that have been selected, this doesnt seem to be the case 😞

     

     

    having manually tested each step, it seems the ALLSELECTED in a variable doesnt pickup the slicer selctions, but the same code as a measure [Selected Types] works fine on its own.

     

    Any ideas?

    • iplaygod's avatar
      iplaygod
      Resolver I
      6 years ago

      Hi Oex 

      Im not 100% sure what the problem is.
      But one question i have for you:

      why are you using ALLSELECTED() ?

      if your data model is has tables connected "properly" then the slicer would filter the table automatically, and if you would run CONCATENATEX on the table you should only pick up the items that have been filtered inside the current filter context?

      what im saying is u should not need ALLSELECTED()?