Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
iplaygod
Resolver I
Resolver I

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!

2 ACCEPTED SOLUTIONS

@iplaygod

 

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

 

 

 

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 )

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

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

Regards
Zubair

Please try my custom visuals

View solution in original post

17 REPLIES 17
Oex
Advocate I
Advocate I

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]

 

TextSearch1.png

 

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 😞

 

TextSearch2.png

 

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?

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
Community Champion
Community Champion

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

 


Regards
Zubair

Please try my custom visuals

@iplaygod

 

See attached file. it will give you an idea

 

measuretolist.png


Regards
Zubair

Please try my custom visuals

@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

@iplaygod

 

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

 

 

 

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 )

 

 


Regards
Zubair

Please try my custom visuals

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

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 )


 

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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@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

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

Regards
Zubair

Please try my custom visuals

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:
Capture.PNG
 
 
 
 
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?

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

Regards,
Tom


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

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:

 

image.png

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.

 

Capture.PNG

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.