Forum Discussion

spandy34's avatar
spandy34
Responsive 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 ClaimRef) where the field named ClassOfBusiness is OT and the field PolicyCode contains the letters REC
 
Can anyone help me please.
 
  • 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.

10 Replies

  • 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

  • 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.

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

        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.

  • spandy34's avatar
    spandy34
    Responsive Resident

     

     

    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 

     

     

     

     

  • spandy34's avatar
    spandy34
    Responsive Resident

    goncalogeraldes Would it be posible for you to help me with this query as this measure is to be included into your previous answer provided - the issue I have is when you have multiple criteria and one of them is contains REC as opposed to = REC

     

    Thanks again for all your help