Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
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
DAX doesn't support the wildcard operators in the conditional statement in CALCULATE.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
101 | |
72 | |
68 | |
45 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |