Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

DAX To Split Measure Text into Separate Rows

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.

1 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.

 

View solution in original post

10 REPLIES 10
CerebusBI
Resolver I
Resolver I

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
Not applicable

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.

 

Anonymous
Not applicable

@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
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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?

 

  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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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.

amitchandak
Super User
Super User

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

or simply product_id unsummarized

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.