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

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