Forum Discussion
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-...
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 )TomMartensiplaygod
Ohh. YesThis 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 )
You can store the value of a MEASURE in a variable and then convert it into a list
For example
Suppose you have a MEASURE
Measure = "1,2,3,5"
You can use it in another measure like this
Measure 2 = VAR mymeasure=SUBSTITUTE([Measure],",","") VAR Mylen=len(mymeasure) VAR mytable=ADDCOLUMNS(GENERATESERIES(1,mylen),"mylist",VALUE(Mid(mymeasure,[Value],1))) VAR mylist=SELECTCOLUMNS(mytable,"list",[mylist]) RETURN CALCULATE(COUNTROWS(Table1),Table1[ID] in mylist)
Zubair_Muhammad
Ok this seems like a very promising start!
Thanks so much for the suggestion!But what happens when I cannot rely on the numbers being the same size in chars,
so lets say the string is:
"1,40,567,4,56708"
ie the numbers are all different lengths...
then what?
how do I iterate over that?
thanks again
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 )
