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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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!