Problem using sumx() with containsstring

[CLANG10]

Hi,

    I am using CONTAINSSTRING to look for a specific text in a column, however i can't find the table/column, so i try using SUMX in the expression to be able to locate the column. An error occur to stating that SUMX does not work with Values containing String. any way to work around this? 

[VijayP]

@CLANG10 

You can split the column in power query or use Add column from example option to extract the required text into another column! 

for example right click on display id and select add column from example option from the menu

then you will find a new column with 'Column 1" heading in extreme right

in the first row you need to just type SIN ( since the first row value is data_sin) and press enter and you will get the values accross the column and press ok to continue

[CLANG10]

I got the data from another live source, so i can't use power query, can only use DAX. Thanks anyway!

Any solution using DAX expression?

[mangaus1111]

Hi @CLANG10 ,

you can use these 2 columns:

Column 1 = REPLACE([Text],1,5,BLANK())

Column 2 = LEFT([Column 1],SEARCH("_",[Column 1])-1)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CLANG10]

Hi seemd like my live data source is preventing me to use data transform/power query nor split any column. I can only create DAX measures, thus i can't test out your solution. Thanks anyway!

[mangaus1111 · Accepted solution]

Hi @CLANG10 ,

try this measure

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CLANG10]

Many Thanks, it works this way!!

[mangaus1111]

to split column by delimeter you can use this button

[mangaus1111]

Hi @CLANG10 ,

is very simple in Power Query using this code

#"Changed Type" = Table.TransformColumnTypes(Facts49_Table,{{"Display ID", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Display ID", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Display ID.1", "Display ID.2", "Display ID.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Display ID.1", type text}, {"Display ID.2", type text}, {"Display ID.3", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Display ID.1", "Display ID.3"})
in
#"Removed Columns"

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CLANG10]

I got the data from another live source, so i can't use power query, can only use DAX. Thanks anyway!

[mangaus1111]

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@CLANG10 

Need to see some datapoint to explain the solution better

[CLANG10]

Hi,

   This is a section of my table, what i want is to find specific text in column "DISPLAY_ID" (E.G. if it contain DATA_UK, to return UK or DATA_USA, to return USA in a new column)...the problem is i can't write the table or column in my expression. Thanks in advance