DAX Commands Count

[spandy34]

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.

[MarkLaf · Accepted solution]

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.

[MarkLaf · Accepted solution]

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.

[spandy34]

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 · Accepted solution]

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.

[spandy34]

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]

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

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.

[MarkLaf]

Depends on exactly what you want.

If you just want the total count to stick in a card where the measure isn't getting filtered (except by filters from other visuals/slicers), you can just remove the DATESYTD filter as @goncalogeraldes recommended.

If you rather would want the total to be repeated per row in a visual (usually you would want this to use as a denominator for calculating a percentage or ratio), then the measure would be similar, but you would want to remove filters. Examples measures below:

Total Recoveries No SRUL_regularcount = 
CALCULATE (
    COUNTROWS ( 'Main Claim Data' ),
    KEEPFILTERS( 'Main Claim Data'[ClassOfBusinessCode] = "OT" ) ,
    KEEPFILTERS( CONTAINSSTRINGEXACT ( 'Main Claim Data'[PolicyCode], "REC" ) )
)

Total Recoveries No SRUL_rowdenominator = 
CALCULATE (
    COUNTROWS ( 'Main Claim Data' ),
    'Main Claim Data'[ClassOfBusinessCode] = "OT",
    CONTAINSSTRINGEXACT ( 'Main Claim Data'[PolicyCode], "REC" ),
    REMOVEFILTERS( 'Main Claim Data' )
)

Total Recoveries No SRUL % = 
DIVIDE( 
    [Total Recoveries No SRUL_regularcount], 
    [Total Recoveries No SRUL_rowdenominator] 
)

Sample model used:

Relationships:

Fin_Calendar --1:M-> 'Main Claim Data'

'Main Claim Data':

Net	PolicyCode	Reference	ClassOfBusinessCode	TransactionDate
$51.02	123RECabc	abcd	OT	5/20/2021
$99.99	Z	efgh	OT	3/12/2022
$121.74	Z	ijkl	A	1/30/2021
$21.65	123REC	admin123	OT	11/12/2021
$82.93	X	mnop	B	8/23/2021
$11.13	RECabcdef	123ADMIN	OT	2/20/2020
$55.10	rec123	SYSADMIN	OT	5/20/2022
$48.88	: ) REC : (	topadministrator	OT	12/3/2022
$73.31	X	000ADMIN0	B	5/20/2021

Fin_Calendar:

Fin_Calendar =
CALENDAR( DATE( 2020, 1, 1 ), DATE( 2022, 12, 31 ) )

Date

1/1/2020

1/2/2020

1/3/2020

1/4/2020

1/5/2020

…

12/27/2022

12/28/2022

12/29/2022

12/30/2022

12/31/2022

[goncalogeraldes]

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

[spandy34]

@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

[spandy34]

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