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.
Hello there spandy34 ! Check if this works:
Count =
CALCULATE (
COUNTROWS ( 'Main Claim Data' ),
/* depending on the type of count you need, you might need to use COUNTA() */
FILTER (
'Main Claim Data',
'Main Claim Data'[ClassOfBusiness] = "OT"
&& CONTAINSSTRINGEXACT ( [PolicyCode], "REC" ) /* you can use CONTAINSSTRING() if not case sensitive */
)
)
Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!
You can also check out my LinkedIn!
Best regards,
Gonçalo Geraldes