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

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.

     

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

    or simply product_id unsummarized

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

  • CerebusBI's avatar
    CerebusBI
    Resolver I
    4 years ago

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

      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's avatar
        CerebusBI
        Resolver I
        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.