Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
DAX
Power BI Desktop
  • CerebusBI's avatar
    CerebusBI
    4 years ago

    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.

     

OwenAuger's avatar
OwenAuger
Super User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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!