Forum Discussion
Problem using sumx() with containsstring
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?
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.
12 Replies
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
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.
I got the data from another live source, so i can't use power query, can only use DAX. Thanks anyway!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
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?
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.
to split column by delimeter you can use this button
