Split Value

[user1112]

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?

[v-bmanikante]

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]

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. 

[Elena_Kalina]

Hi @user1112 

Try this:

SplitValues = 
VAR SelectedValue = SELECTEDVALUE(YourTable[ColumnToSplit])
VAR SplitArray = PATHITEMREPLACE(SUBSTITUTE(SelectedValue, ",", "|"))
RETURN
CONCATENATEX(SplitArray, PATHITEM(SplitArray, [Value]), ", ")

[user1112]

There is no 

PATHITEMREPLACE

 function in dax.

[Elena_Kalina · Accepted solution]

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.

[user1112]

I got the error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. 

[Elena_Kalina]

Did you create a table like I have in my recommendations or a measure?

[user1112]

I missed that step, now it works, thankyou a lot