Getting a distinct count of records based on criteria

[szub]

Hi,

I am trying to find how to get a distinct count of records that contain certain criteria.  Below is sample data.  I need a "Claim" number to be counted once to if "Adjsted by Line" is "Yes" on one or more lines.  Ultimately I want to use this to get a percentage of claims that had had an adjustment.  Right now I can only get it by percentage of lines since there can be multiple lines on a claim. 

I have tried to use the following formula, but when I export a table to Excel to validate the numbers, it is not correct and providing a higher count:  Adjusted Claim = IF('TABLE 1'[Labor Adjusted by Line]="Yes",CALCULATE(DISTINCTCOUNT(TABLE 2[Claim #])))

Claim #	Type	Request Qty	Approved Qty	Adjusted by Line
1	HOURLY	11	5	Yes
1	MILEAGE	50	50	No
1	TRAVEL	1.25	1.25	No
2	HOURLY	1	1	No
2	HOURLY	3	3	No
3	HOURLY	8	6	Yes
3	MILEAGE	100	100	No
3	TRAVEL	2	2	No
4	HOURLY	6	4	Yes
4	MILEAGE	125	100	Yes
4	TRAVEL	2	2	No

I appreciate any guidance.

Regards

[jdbuchanan71 · Accepted solution]

Hello @szub 

We need just a couple measures.  First, one to just count the distinct claims.

Claim Count = DISTINCTCOUNT ( 'Table'[Claim #] )

Then we can do one to count just the adjusted claims.

Adjusted Claim Count = 
CALCULATE (
    [Claim Count],
    'Table'[Adjusted by Line] = "Yes"
)

And finally the % of adjusted claims.

Adjusted Claim % = DIVIDE ( [Adjusted Claim Count], [Claim Count] )

[amitchandak · Accepted solution]

Can you try like

CALCULATE(DISTINCTCOUNT(TABLE 2[Claim #]), filter('TABLE 1','TABLE 1'[Labor Adjusted by Line]="Yes"))

[jdbuchanan71 · Accepted solution]

Hello @szub 

We need just a couple measures.  First, one to just count the distinct claims.

Claim Count = DISTINCTCOUNT ( 'Table'[Claim #] )

Then we can do one to count just the adjusted claims.

Adjusted Claim Count = 
CALCULATE (
    [Claim Count],
    'Table'[Adjusted by Line] = "Yes"
)

And finally the % of adjusted claims.

Adjusted Claim % = DIVIDE ( [Adjusted Claim Count], [Claim Count] )

[szub]

@jdbuchanan71 Thank you - this worked too!

[szub]

@amitchandak Thank you so much, this worked!