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 )
tondeli's avatar
tondeli
Advocate I
7 years ago

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's avatar
TomMartens
Super User
7 years ago

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