Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ahiemstra
Helper I
Helper I

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

I have a table with daily referrals to a variety of healthcare providers. The table looks something like this:

 

Referral DateReferral PriorityPatient IDReferral StatusAssigned to
17-Apr-23Non-Urgent456BookedKathy H
09-May-23Urgent7664BookedKathy H
18-May-23Non-Urgent25BookedJoe C
25-May-23Urgent2457945BookedMichael M
06-Jun-23Urgent345BookedMichael M
14-Jun-23Urgent3467BookedMichael M
23-Jun-23Non-Urgent23834BookedKathy H
26-Jun-23Urgent748BookedJane D
07-Jul-23Urgent634634BookedKathy H
14-Jul-23Non-Urgent473465BookedKathy H
26-Jul-23Urgent754BookedJoe C
03-Aug-23Non-Urgent26853BookedMichael M
15-Aug-23Urgent7453BookedJoe C
17-Aug-23Non-Urgent574534BookedMichael M
25-Aug-23Non-Urgent3684BookedJoe C
07-Sep-23Non-Urgent5436BookedJane D
12-Sep-23Non-Urgent745BookedJane 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 ProviderFactor
Kathy H1.000
Joe C1.250
Michael M2.500
Jane D2.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 H55
Joe C45
Michael M513
Jane D38
1 ACCEPTED SOLUTION
Dangar332
Super User
Super User

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

Dangar332_0-1703164628714.png

 

Dangar332_1-1703164782127.png

 

 

View solution in original post

1 REPLY 1
Dangar332
Super User
Super User

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

Dangar332_0-1703164628714.png

 

Dangar332_1-1703164782127.png

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors