cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors