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

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

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors