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.
Thanks 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 post
Filtered 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.