Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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] )
Can you try like
CALCULATE(DISTINCTCOUNT(TABLE 2[Claim #]), filter('TABLE 1','TABLE 1'[Labor Adjusted by Line]="Yes"))
Can you try like
CALCULATE(DISTINCTCOUNT(TABLE 2[Claim #]), filter('TABLE 1','TABLE 1'[Labor Adjusted by Line]="Yes"))
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] )