cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jakeryan56 Regular Visitor
Regular Visitor

SUM filter if contains text - Issue

Hi,

 

I am trying to calculate SUM of a field if another field contains a value.

I tried the following formulas but neither work:

 

I tried using a wildcard symbol (not sure if it works in DAX) but no data showed up:
Consumer Handset Plan = CALCULATE(SUM('Sales Data'[Qty]),'Sales Data'[Plan Type] = "*Mobile*")

 

This one gives me an error:

Consumer Handset Plan = CALCULATE(SUM('Sales Data'[Qty]),Contains('Sales Data','Sales Data'[Plan Type],"Mobile")) 

 

Cheers

1 ACCEPTED SOLUTION

Accepted Solutions
Steve_Wheeler Established Member
Established Member

Re: SUM filter if contains text - Issue

@jakeryan56,

You could try something like this:

 

Consumer Handset Plan =
CALCULATE (
    SUM( 'Sales Data'[Qty] ),
    FILTER ( 'Sales Data', FIND ( "Mobile", 'Sales Data'[Plan Type],, 0 ) <> 0 )
)

 

Note that FIND is case-sensitive. Check this link for details on using FIND vs. SEARCH etc. - https://www.sqlbi.com/articles/from-sql-to-dax-string-comparison/

 

You may also find it quite slow with large datasets.  If you want to use that 'category' of Plan Type for other reasons - e.g. adding a slicer for Plan Type Category, consider adding a conditional column for every row in Power Query when you get the data, using Text.Contains functions etc.

 

View solution in original post

4 REPLIES 4
BhaveshPatel Super Contributor
Super Contributor

Re: SUM filter if contains text - Issue

DAX doesn't support the wildcard operators in the conditional statement in CALCULATE. 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Steve_Wheeler Established Member
Established Member

Re: SUM filter if contains text - Issue

@jakeryan56,

You could try something like this:

 

Consumer Handset Plan =
CALCULATE (
    SUM( 'Sales Data'[Qty] ),
    FILTER ( 'Sales Data', FIND ( "Mobile", 'Sales Data'[Plan Type],, 0 ) <> 0 )
)

 

Note that FIND is case-sensitive. Check this link for details on using FIND vs. SEARCH etc. - https://www.sqlbi.com/articles/from-sql-to-dax-string-comparison/

 

You may also find it quite slow with large datasets.  If you want to use that 'category' of Plan Type for other reasons - e.g. adding a slicer for Plan Type Category, consider adding a conditional column for every row in Power Query when you get the data, using Text.Contains functions etc.

 

View solution in original post

jakeryan56 Regular Visitor
Regular Visitor

Re: SUM filter if contains text - Issue

Thankyou so much!! This worked a treat 

Highlighted
erolyucel Frequent Visitor
Frequent Visitor

Re: SUM filter if contains text - Issue

Thanks a lot for the text filter tip, spent a couple of hours to find your solution. Take care

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 266 members 2,690 guests
Please welcome our newest community members: