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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chrisjr
Helper IV
Helper IV

how to exlude text from formula

Hello All,

 

I have the following table:

 

NameValue
CocaLight5
CocaZero6
Zero3
Extra1
PesMax2

 

I want to calculate the sum of the values but exlude all the name containing "Coca" in its name. 

What would be the formula ?

 

Thanks

1 ACCEPTED SOLUTION
Barthel
Solution Sage
Solution Sage

Hey @Chrisjr,

You can use the CONTAINSTRING function in combination with a CALCULATE for this. SUM the 'Value' column, where the 'Name' does not contain the value 'Coca':

 

Formula = 
CALCULATE ( 
    SUM ( 'Table'[Value] ),
    KEEPFILTERS ( NOT CONTAINSSTRING ( 'Table'[Name], "Coca" ) )
)

 

If you need 'Coca' to be case sensitive, use the CONTAINTSSTRINGEXACT function instead of CONTAINSSTRING.

View solution in original post

2 REPLIES 2
Chrisjr
Helper IV
Helper IV

thanks @Barthel 

Barthel
Solution Sage
Solution Sage

Hey @Chrisjr,

You can use the CONTAINSTRING function in combination with a CALCULATE for this. SUM the 'Value' column, where the 'Name' does not contain the value 'Coca':

 

Formula = 
CALCULATE ( 
    SUM ( 'Table'[Value] ),
    KEEPFILTERS ( NOT CONTAINSSTRING ( 'Table'[Name], "Coca" ) )
)

 

If you need 'Coca' to be case sensitive, use the CONTAINTSSTRINGEXACT function instead of CONTAINSSTRING.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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