cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jakeryan56
Helper II
Helper II

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
Anonymous
Not applicable

@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

5 REPLIES 5
Anonymous
Not applicable

@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

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

 

thank you very much @Anonymous , it solves my issue! I used SEARCH instead of FIND which was more appropriate to my need (mention that for others: I wanted to took only the rows containing a piece of text, not the whole text, eg, all the row with *text* in a specific dimension, whatever could be before and after).

Thankyou so much!! This worked a treat 

BhaveshPatel
Community Champion
Community Champion

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!