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

[iplaygod]

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!

[Zubair_Muhammad · Accepted solution]

@iplaygod

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

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 )

[Oex]

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]

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

[Zubair_Muhammad]

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

[Zubair_Muhammad]

@iplaygod

See attached file. it will give you an idea

[iplaygod]

@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 · Accepted solution]

@iplaygod

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

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]

@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]

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]

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]

@Zubair_Muhammad@TomMartens

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 · Accepted solution]

@TomMartens@iplaygod

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 )

[tondeli]

First, this solution looks cool!! I've tried but can't get it to work...

I have a date table and I use date as a slicer and I allow users to choose multiple values.

I want to show all dates from my data table and calculate sum of sales from fact table per day . For the next column I'd like to show sales for selected dates.

Date - TotalSales - SelectedSales

This one works:

SalesSelectedDates = 
calculate([SumSales];Calendar[Date] in {"22.2.2018";"23.2.2018";"23.3.2018";"1.4.2018";"5.5.2018"})

I created following measure for selected dates:

SelectedDates = 
concatenatex(allselected(Calendar[Date]);Calendar[Date];", ")

I've added above measure to a card visualization and I can see following:

Next I've tried to copy your solution:

SalesSelectedDates2 =

var selectedDates=substitute([SelectedDates];", ";"|")
var selectionLEN=pathlength(selectedDates)
var selectionTable=addcolumns(generateseries(1;selectionLEN);"List";value(pathitem(selectedDates;[Value])))
var selectionList=selectcolumns(selectionTable;"Dates";[List])

return (
calculate([SumSales];Calendar[Date] in selectionList)
)

For me this calculates total sales from my fact and show it for each date. What I am doing wrong?

[TomMartens]

Hey, I assume that there is a relationship between your date table and your fact table.

Regards,
Tom

[TomMartens]

Hey, I'm not totally sure what's going on your side,
maybe you might consider to create a pbix with some sample data, upload the pbix to onedrive or dropbox and share the link.

Nevertheless, this should work:

SalesSelectedDates2 =
calculate([SumSales];Calendar[Date] in allselected(Calendar[Date])) 

If it doesn't, you can debug this issue by just adding a measure like this:

_debug = 
COUNTROWS(ALLSELECTED('Calendar'[Date]))

Add this measure to your table and make sure that the number meets your expectation, meaning the number of selected dates in your slicer.

Regards,
Tom 

[tondeli]

I have no idea what is going on but with my Power BI Desktop lately. I am using Feb-19 version.

DatePCS = countrows(allselected(Calendar[Date]))

Measure gives results 5 -as it should.

I have a table that I don't want to control with Date slicer. With date slicer I just want to control how my measure will be calculated.

Measure calculates total amount for every date in my table.

I have relationship between fact table and date table. I have also marked that as Date Table.

Here is pbix file: SelectedDates Calculation.pbix

[TomMartens]

Hey @tondeli ,

I'm not totally sure what your expected result is, but I created this measure:

Sum Of Subtotal by Calendar Date Selected = 
CALCULATE(
    [Sales]
    ,TREATAS(VALUES('Calendar'[Date]),'Sales SalesOrderHeader'[OrderDate])
)

This allows to create this:

As you can see, the measure now sums the values of the the selected dates.

Please be aware that I use the column OrderDate from the sales table instead of the date column, as in my solution both tables are no longer related.

To achieve this, I also changed some things in your pbix:

• I removed the relationship between your Calendar table and your Sales table, from my understanding you just need the Calendar table to select the dates (if this is not the case - create another Calendar table for the date selection)
• I also re-established the interaction, even this does not matter, as both are no longer related. This has also been the reason why your Sales measure returns the sum of the column Subtotal across all rows.
  
  

I'm not sure, if this is what you are looking for, in case it's not. Please start a new thread and also describe your expected result.

Regards,
Tom

[tondeli]

I would like my new measure act like:

SalesSelectedDates_values = 
calculate('Sales SalesOrderHeader'[Sales];'Calendar'[DateText] in {"8.7.2001";"10.7.2001";"19.7.2001"})

I just want to control the list with my date selection. I found a workaround but I'd love to get the above measure to work.

I created new table visualization and I'm controlling that with date slicer (from calendar table) by filtering dates. I added date (order date) from fact table and Sales measure as columns.

I created new measure and added that to my table:

Sales_ALL = calculate('Sales SalesOrderHeader'[Sales];all('Calendar'[Date]))

This one shows values for each date apart from my date selection in date slicer.

Only problem with this is that I'm not showing dates that doesn't have any data (date from calendar vs. order date from fact).