Forum Discussion
DAX To Split Measure Text into Separate Rows
Hi Anonymous ,
A measure always needs to have a scalar result: A number, a text, a date...
but never a table or a list.
I agree with OwenAuger: maybe we need to understand what's your raw data and what you want to see in your report / visual.
Could it be possible that putting the raw Data[product_id] into the Slicer and also into the table column?
Then choosing some Ids in the slicer would also filter the table accordingly.
Hi OwenAuger ,
Thank you for helping. Apologies if I wasn't clear, but I was hoping to split the values into separate rows, so that each row could be used on their own. I know this is easy to do in power query with the 'split by delimiter' functionality, but I don't know if this is reproduceable in DAX.
| Product IDs |
| 100 |
| 101 |
| 102 |
| 406 |
| 407 |
| 500 |
Many thanks,
Jess
Hi Jess
Ah I see, sorry for the confusion at my end.
I see others are already in the discussion so you may get an answer from someone else anyway 🙂
My main question is: where do you need to use the column of Product IDs?
- If you need it as part of a measure, VALUES ( Data[product_id] ) already produces this result (you could also use DISTINCT or some other functions). No need to concatenate and split again.
- If you need it as part of a calculated table, again VALUES ( Data[product_id] ) already produces this result.
- If you want to see product_id values on rows of a table visual (or similar), just place product_id as a field in the visual.
Having said all that, if for some reason you need to split a comma-delimited list in DAX, this sort of expression will do it (with <Comma Separated List> replaced by an appropriate expression):
VAR CommaSeparatedList =
<Comma Separated List>
VAR BarSeparatedList =
SUBSTITUTE ( CommaSeparatedList, ",", "|" )
VAR Length =
PATHLENGTH ( BarSeparatedList )
VAR Result =
SELECTCOLUMNS (
GENERATESERIES ( 1, Length ),
"Product ID", PATHITEM ( BarSeparatedList, [Value] )
)
RETURN
ResultThanks for looking further into this! Basically, the product ID's I'd like to split are dynamically filtered by a product ID slicer. So, whichever product IDs someone selects from the slicer will appear in this measure. Whenever I try to use something like VALUES, or DISTINCT, I get the error: "A table of multiple values was selected where a single value was expected".
Your DAX is exactly what I'm looking for, thanks, but unfortunately, I get this same multiple values error when I try it in PBI. I'm probably just missing something obvious!Hey! I am having this exact problem. OwenAuger the options you have given are correct. But imagine a user is selecting multiple filters from a filter and I want to get the names of all the selected filters, IN THE CORRECT SEQUENCE, as rows.
Anonymous is correct in that if we use VALUES or DISTINCT, the error message "A table of multiple values was selected where a single value was expected" appears. However, the DAX in his original postFiltered Product IDs = CONCATENATEX ( VALUES ( Data[product_id] ) , [product_id] , ",")does, as he says "take him halfway there" by providing him with that list of selected values from the filters in the right order. But say, I want to display that list in the form of rows or as a numbered list in the sequence of selections made by the user. That is where I am starting to struggle as well.
Although OwenAuger 's solution of just dragging that field on a new visual does show the selected values as a list BUT it does that in an alphabetical order NOT in the order of selection.
Hope this use case helps visualize where this might be useful.
