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

    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'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!