DAX to Count values from a Table and multiply by value in another Table

[ahiemstra]

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

[Dangar332 · Accepted 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