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 )
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()?