cancel
Showing results for
Did you mean:
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 # 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

2 ACCEPTED SOLUTIONS

Accepted Solutions
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],
)```

And finally the % of adjusted claims.

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

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

4 REPLIES 4
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],
)```

And finally the % of adjusted claims.

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

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

Regular Visitor

## Re: Getting a distinct count of records based on criteria

@amitchandak Thank you so much, this worked!

Regular Visitor

## Re: Getting a distinct count of records based on criteria

@jdbuchanan71 Thank you - this worked too!

Announcements

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

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

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)