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.
I am trying to create a measure within a table called Main Claim Data where it calculates the sum of a field currency field called (Net) where the field named ClassOfBusiness is OT and the field PolicyCode contains the letters REC