Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a table with daily referrals to a variety of healthcare providers. The table looks something like this:
Referral Date | Referral Priority | Patient ID | Referral Status | Assigned to |
17-Apr-23 | Non-Urgent | 456 | Booked | Kathy H |
09-May-23 | Urgent | 7664 | Booked | Kathy H |
18-May-23 | Non-Urgent | 25 | Booked | Joe C |
25-May-23 | Urgent | 2457945 | Booked | Michael M |
06-Jun-23 | Urgent | 345 | Booked | Michael M |
14-Jun-23 | Urgent | 3467 | Booked | Michael M |
23-Jun-23 | Non-Urgent | 23834 | Booked | Kathy H |
26-Jun-23 | Urgent | 748 | Booked | Jane D |
07-Jul-23 | Urgent | 634634 | Booked | Kathy H |
14-Jul-23 | Non-Urgent | 473465 | Booked | Kathy H |
26-Jul-23 | Urgent | 754 | Booked | Joe C |
03-Aug-23 | Non-Urgent | 26853 | Booked | Michael M |
15-Aug-23 | Urgent | 7453 | Booked | Joe C |
17-Aug-23 | Non-Urgent | 574534 | Booked | Michael M |
25-Aug-23 | Non-Urgent | 3684 | Booked | Joe C |
07-Sep-23 | Non-Urgent | 5436 | Booked | Jane D |
12-Sep-23 | Non-Urgent | 745 | Booked | Jane D |
I have another table which lists the healthcare providers and contains a ‘factor’ that the referral count (from the table above) must be multiplied by. The rationale is many of these healthcare providers are a different full-time equivalent (FTE) status so to compare referral volumes, I need to look at all provider's referral volumes as though they were a 1.00 FTE (e.g., adjust volumes based on FTE status.)
The table with the healthcare providers and the ‘factor’ to multiply by looks like this:
Healthcare Provider | Factor |
Kathy H | 1.000 |
Joe C | 1.250 |
Michael M | 2.500 |
Jane D | 2.500 |
I’m looking to create a DAX which can count the number of referrals for Michael M for example (5 referrals) and multiply it by the ‘factor’ for Michael M (2.500).
In the end I want to be able to create a table visual which looks like this:
Health Care Provider | # Referrals | # Referrals Adjusted |
Kathy H | 5 | 5 |
Joe C | 4 | 5 |
Michael M | 5 | 13 |
Jane D | 3 | 8 |
Solved! Go to Solution.
hi, @ahiemstra
try below measure
Referrals =
COUNT(referral[Assigned to])
AND
Referrals Adjusted =
COUNT(referral[Assigned to])*MAX(factor[Factor])
relationship bw Assigned to and Health care provide
hi, @ahiemstra
try below measure
Referrals =
COUNT(referral[Assigned to])
AND
Referrals Adjusted =
COUNT(referral[Assigned to])*MAX(factor[Factor])
relationship bw Assigned to and Health care provide
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
11 | |
10 | |
8 |