DAX To Split Measure Text into Separate Rows

[Anonymous]

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.

[CerebusBI]

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.

[Anonymous]

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!

[CerebusBI · Accepted 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.

[Anonymous]

@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

[OwenAuger]

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

[Anonymous]

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

[OwenAuger]

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?

1. 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.
2. If you need it as part of a calculated table, again VALUES ( Data[product_id] ) already produces this result. 
3. 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.

[Anonymous]

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!

[rushi]

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.

[amitchandak]

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

or simply product_id unsummarized