Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm having trouble writing DAX to perform what I need. I have a measure that creates a list of the values selected from a filter for a product ID:
Filtered Product IDs = CONCATENATEX ( VALUES ( Data[product_id] ) , [product_id] , ",")
However, the output of this is a list of values split by comma:
Filtered Product IDs |
100, 101, 102, 406, 407, 500 |
I would like to split this into separate rows, with the delimiter being the comma:
Product IDs |
100 |
101 |
102 |
406 |
407 |
500 |
I've seen some other similar issues, which have been solved using PATHITEM, but I can't get it to work for my issue.
Any help would by much appreciated, thanks.
Solved! Go to Solution.
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 @Anonymous ,
Your problem sounds interesting, but I'm not sure that I understand it correctly:
According to your measure, you have a table "Data" with a column "product_id". And your measure refers to it.
In the end, you want to create a column with product IDs.
Why do you use the measure at all?
Why don't you just filter on the given column with the product_id?
I'm sure, I'm misunderstanding something here.
Please give me a hint.
Hi @CerebusBI ,
I don't think I was as clear as I thought I was, sorry! Basically, I have a slicer for product id, and I'd like to be able to have whichever values are selected in this slicer as separate rows in a measure. This formula:
CONCATENATEX ( VALUES ( Data[product_id] ) , [product_id] , ",")
gets me halfway there, as it shows the product IDs selected in the slicer, but all in one row, which makes using this data difficult.
I this makes it a bit clearer, and thanks for you help!
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.
@CerebusBI Ah ok, I was worried that might be the case. I'll go back to the drawing board and see if I can design what I need differently. Thanks so much for your help
Hi @Anonymous
You can use the "Line feed" character UNICHAR(10) if you require text split across multiple lines.
I recall in the past that line feeds didn't always display correctly, but they appear to work now for values within card and table visuals at least.
Sample measure:
Filtered Product IDs =
CONCATENATEX (
VALUES ( Data[product_id] ),
Data[product_id],
"," & UNICHAR ( 10 )
)
Regards,
Owen
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?
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
Result
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.
@Anonymous , Visual have product_id as ungrouped column then only this will work
or simply product_id unsummarized
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
26 | |
20 | |
15 | |
11 |
User | Count |
---|---|
77 | |
62 | |
44 | |
17 | |
12 |