Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
CLANG10
Helper I
Helper I

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? 

1 ACCEPTED SOLUTION

Hi @CLANG10 ,

try this measure

mangaus1111_0-1666942303987.png

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

 

View solution in original post

12 REPLIES 12
VijayP
Super User
Super User

@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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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.

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!

Hi @CLANG10 ,

try this measure

mangaus1111_0-1666942303987.png

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

 

Many Thanks, it works this way!!

mangaus1111
Solution Sage
Solution Sage

to split column by delimeter you can use this button

mangaus1111_0-1666779306358.png

 

mangaus1111
Solution Sage
Solution Sage

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.

VijayP
Super User
Super User

@CLANG10 

Need to see some datapoint to explain the solution better




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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 

CLANG10_0-1666778302138.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.