cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
szub Regular Visitor
Regular Visitor

Getting a distinct count of records based on criteria

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 #TypeRequest QtyApproved QtyAdjusted by Line
1HOURLY115Yes
1MILEAGE5050No
1TRAVEL1.251.25No
2HOURLY11No
2HOURLY33No
3HOURLY86Yes
3MILEAGE100100No
3TRAVEL22No
4HOURLY64Yes
4MILEAGE125100Yes
4TRAVEL22No

 

I appreciate any guidance.

 

Regards

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Getting a distinct count of records based on criteria

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

ClaimCount.jpg

View solution in original post

amitchandak Super Contributor
Super Contributor

Re: Getting a distinct count of records based on criteria

Can you try like

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

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Getting a distinct count of records based on criteria

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

ClaimCount.jpg

View solution in original post

amitchandak Super Contributor
Super Contributor

Re: Getting a distinct count of records based on criteria

Can you try like

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

View solution in original post

szub Regular Visitor
Regular Visitor

Re: Getting a distinct count of records based on criteria

@amitchandak Thank you so much, this worked!

szub Regular Visitor
Regular Visitor

Re: Getting a distinct count of records based on criteria

@jdbuchanan71 Thank you - this worked too!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)