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.
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.
- spandy344 years ago
Responsive Resident
Thats brilliant - can we also include the function is the measure below where it also includes in the SUM records where the field named (Reference) contains text ADMIN in the field
Test = CALCULATE( SUM( 'Main Claim Data'[Net] ), 'Main Claim Data'[ClassOfBusinessCode] = "OT", FILTER( 'Main Claim Data', ISNUMBER( SEARCH( "REC", 'Main Claim Data'[PolicyCode], , BLANK() ) ) ) )- MarkLaf4 years ago
Super User
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.
- spandy344 years ago
Responsive Resident
Thats brilliant - thank you -
I have the following measure but instead of calculating the amount of records Year to Date with the criteria, I want count the total ever, how can I amend to count all
Total Recoveries No Successful Recoveries Uninsured Loss =
CALCULATE (
COUNTROWS ( 'Main Claim Data' ),
FILTER (
'Main Claim Data',
'Main Claim Data'[ClassOfBusinessCode] = "OT"
&& CONTAINSSTRINGEXACT ( [PolicyCode], "REC" )),
DATESYTD('Fin_Calendar'[Date]))