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

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

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