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