Forum Discussion

user1112's avatar
user1112
New Member
1 year ago
Solved

Split Value

Hello, I have a table with multiple rows and columns and I want to split the value in one column by a delimiter (comma), I can't flatten the table because I have too many rows, I want to split dynamically and I click a row in a table visual but DAX doesn't have a split function. Do you have any ideas how I can work around this?

Need Help
Show and Tell
Tips and Tricks
  • Elena_Kalina's avatar
    Elena_Kalina
    1 year ago

    Hi user1112 

    Apologies for wasting your time by providing the incorrect formula. Perhaps this solution will help you:

    1. Create a calculated table with just IDs and split values:

    SplitRelationships =  
VAR SourceTable = SUMMARIZE(DataTable, DataTable[ID], DataTable[Tags])  
RETURN  
ADDCOLUMNS(  
    GENERATE(  
        SourceTable,  
        VAR ItemCount = PATHLENGTH(SUBSTITUTE([Tags], ",", "|"))  
        RETURN GENERATESERIES(1, ItemCount, 1)  
    ),  
    "SplitValue", PATHITEM(SUBSTITUTE([Tags], ",", "|"), [Value], TEXT)  
)

    2. Create relationships between this table and your dimension tables.

    3. Use this table in visuals when you need to filter by split values.

     

    If this post helps, then please  don't forget to give a "Kudos" – I’d truly appreciate it! 

    Thank you.

8 Replies

  • Elena_Kalina's avatar
    Elena_Kalina
    Solution Sage
    1 year ago

    Hi user1112 

    Try this:

    SplitValues = 
VAR SelectedValue = SELECTEDVALUE(YourTable[ColumnToSplit])
VAR SplitArray = PATHITEMREPLACE(SUBSTITUTE(SelectedValue, ",", "|"))
RETURN
CONCATENATEX(SplitArray, PATHITEM(SplitArray, [Value]), ", ")
    • user1112's avatar
      user1112
      New Member
      1 year ago

      There is no 

      PATHITEMREPLACE

       function in dax.

      • Elena_Kalina's avatar
        Elena_Kalina
        Solution Sage
        1 year ago

        Hi user1112 

        Apologies for wasting your time by providing the incorrect formula. Perhaps this solution will help you:

        1. Create a calculated table with just IDs and split values:

        SplitRelationships =  
VAR SourceTable = SUMMARIZE(DataTable, DataTable[ID], DataTable[Tags])  
RETURN  
ADDCOLUMNS(  
    GENERATE(  
        SourceTable,  
        VAR ItemCount = PATHLENGTH(SUBSTITUTE([Tags], ",", "|"))  
        RETURN GENERATESERIES(1, ItemCount, 1)  
    ),  
    "SplitValue", PATHITEM(SUBSTITUTE([Tags], ",", "|"), [Value], TEXT)  
)

        2. Create relationships between this table and your dimension tables.

        3. Use this table in visuals when you need to filter by split values.

         

        If this post helps, then please  don't forget to give a "Kudos" – I’d truly appreciate it! 

        Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi user1112 ,

     

    Thank you for reaching out to Microsoft Fabric Community Forum.

    Elena_Kalina Thank you for your quick response.

     

    Create a Disconnected Index Table as below

     

    IndexTable = GENERATESERIES(1, 10, 1)

     

    Then Create a Measure to split Individual Tags as below

     

    SelectedTag =

    VAR SelectedRow = SELECTEDVALUE(DataTable[Tags])

    RETURN

    PATHITEM(SUBSTITUTE(SelectedRow, ",", "|"), SELECTEDVALUE(IndexTable[Value]), TEXT)

     

     

    Please refer the attached .pbix file.

     

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

     

    Regards,

    B Manikanteswara Reddy

    • user1112's avatar
      user1112
      New Member
      1 year ago

      Well, it works but I got the following issue, because your method is using a measure to split the value I cant use relationships with the measure.