topic Re: DAX To Split Measure Text into Separate Rows in DAX Commands and Tips

DAX To Split Measure Text into Separate Rows

Anonymous — Mon, 24 Jan 2022 11:36:51 GMT

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.

Re: DAX To Split Measure Text into Separate Rows

amitchandak — Mon, 24 Jan 2022 12:06:22 GMT

Anonymous , Visual have product_id as ungrouped column then only this will work

or simply product_id unsummarized

Re: DAX To Split Measure Text into Separate Rows

OwenAuger — Mon, 24 Jan 2022 12:20:25 GMT

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

Re: DAX To Split Measure Text into Separate Rows

Anonymous — Mon, 24 Jan 2022 13:51:30 GMT

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

Re: DAX To Split Measure Text into Separate Rows

CerebusBI — Mon, 24 Jan 2022 14:26:10 GMT

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.

Re: DAX To Split Measure Text into Separate Rows

OwenAuger — Mon, 24 Jan 2022 20:26:39 GMT

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 Result

Example on dax.do.

Re: DAX To Split Measure Text into Separate Rows

Anonymous — Tue, 25 Jan 2022 15:23:52 GMT

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!

Re: DAX To Split Measure Text into Separate Rows

Anonymous — Tue, 25 Jan 2022 15:33:57 GMT

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!

Re: DAX To Split Measure Text into Separate Rows

CerebusBI — Tue, 25 Jan 2022 16:31:11 GMT

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.

Re: DAX To Split Measure Text into Separate Rows

Anonymous — Tue, 01 Feb 2022 10:41:00 GMT

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

Re: DAX To Split Measure Text into Separate Rows

rushi — Mon, 19 Jun 2023 04:34:49 GMT

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.