Forum Discussion

spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

DAX Commands Count

Hi   I am just learning about DAX and was wondering if someone could help.   I am trying to create a measure within a table called Main Claim Data where I count the number of records (field name ...
  • MarkLaf's avatar
    4 years ago

    The below measure works for the following sample data:

    NetClassOfBusinessPolicyCode
    $51.02OT123RECabc
    $99.99OTZ
    $121.74AZ
    $21.65BY
    $82.93BX
    $11.13OTrecabcdef

     

     

    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.

  • MarkLaf's avatar
    MarkLaf
    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:

    NetClassOfBusinessPolicyCodeReference
    $51.02OT123RECabcabcd
    $99.99OTZefgh
    $121.74AZijkl
    $21.65BYadmin123
    $82.93BXmnop
    $11.13OTrecabcdef123admin
    $55.1OTrec123SYSADMIN
    $48.88OT: ) REC : (topadministrator
    $73.31BX000admin0

     

    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.