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

Zubair_MuhammadTomMartens

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

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?

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 

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:

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

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

I am trying to use this code but I am getting Function 'COUNTROW' does not support comparing values of type Number with values of type Text. My ultimate goal is instead of counting the rows where items in the list are found, I would like to return the value that was found. Using the OP example: looking up values 1-7. If values 3,6,7 are found. I want to return a concatenated list showing values 3,6 and 7 were found.