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.
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
- 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]))
- goncalogeraldes4 years ago
Super User
spandy34 Simply remove the FILTER() from the function. I have assumed that when you said you wanted to count records, records = rows, hence my suggestion of using COUNTROWS(). If you need to count events or IDs, use COUNT() or COUNTA() (does not count blank values) or if you need distinct values use DISTINCTCOUNT(). Anyhow, your measure can look something like:
Total Recoveries No Successful Recoveries Uninsured Loss = COUNTROWS ( 'Main Claim Data' )For additional help, please @ me in your reply!
You can also check out my LinkedIn!
Best regards,
Gonçalo Geraldes