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 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
      Icon for Responsive Resident rankResponsive 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
        Icon for Super User rankSuper 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
    Icon for Responsive Resident rankResponsive 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
    Icon for Responsive Resident rankResponsive 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