Forum Discussion
DAX Commands Count
- 4 years ago
The below measure works for the following sample data:
Net ClassOfBusiness PolicyCode $51.02 OT 123RECabc $99.99 OT Z $121.74 A Z $21.65 B Y $82.93 B X $11.13 OT recabcdef Measure = CALCULATE( SUM( 'Main Claim Data'[Net] ), 'Main Claim Data'[ClassOfBusiness] = "OT", FILTER( 'Main Claim Data', ISNUMBER( SEARCH( "REC", 'Main Claim Data'[PolicyCode], , BLANK() ) ) ) )Note that if the check against "REC" needs to be case-sensitive, then you'll want to use FIND instead of SEARCH.
- 4 years ago
Sure can. See the below. I incorporated CONTAINSSTRING used by goncalogeraldes as that has the same effect as ISNUMBER/SEARCH but is more readable (+1 didn't know about those functions). Also, I refreshed myself on best practices for multiple filter arguments at the following, which is why the syntax is a little different from before for the filters: https://www.sqlbi.com/articles/specifying-multiple-filter-conditions-in-calculate/
Measure2 = CALCULATE( SUM( 'Main Claim Data'[Net] ), KEEPFILTERS( 'Main Claim Data'[ClassOfBusiness] = "OT" ), KEEPFILTERS( CONTAINSSTRING( 'Main Claim Data'[PolicyCode], "REC" ) ), KEEPFILTERS( CONTAINSSTRING( 'Main Claim Data'[Reference], "ADMIN" ) ) )Output (Measure is from previous answer, Measure2 is the new measure above):
Sample data used:
Net ClassOfBusiness PolicyCode Reference $51.02 OT 123RECabc abcd $99.99 OT Z efgh $121.74 A Z ijkl $21.65 B Y admin123 $82.93 B X mnop $11.13 OT recabcdef 123admin $55.1 OT rec123 SYSADMIN $48.88 OT : ) REC : ( topadministrator $73.31 B X 000admin0 As goncalogeraldes mentioned, use CONTAINSSTRINGEXACT instead of CONTAINSSTRING if you need case-sensitivity.
Also, I'm assuming the new filter is another AND criteria. As long as you need AND logic on criteria, you can add each as a separate filter argument in CALCULATE. If you need OR logic, you'll have to combine (just what needs to be OR'd) in one filter argument.
goncalogeraldes Would it be posible for you to help me with this query as this measure is to be included into your previous answer provided - the issue I have is when you have multiple criteria and one of them is contains REC as opposed to = REC
Thanks again for all your help