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 ...
spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
4 years ago

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

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

     

    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]))

    • goncalogeraldes's avatar
      goncalogeraldes
      Icon for Super User rankSuper User
      4 years ago

      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

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

        Hiya - but it will need to be all records where ClassofBusinessCode = OT and PolicyCode contains REC - sorry when I said all records I meant all within those parameters not matter what the date range.