cancel
Showing results 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

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 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
1 ACCEPTED SOLUTION
Super User

hi, @ahiemstra

try below measure

`````` Referrals =
COUNT(referral[Assigned to])

AND

COUNT(referral[Assigned to])*MAX(factor[Factor]) ``````

relationship bw Assigned to and  Health care provide

Super User

hi, @ahiemstra

try below measure

`````` Referrals =
COUNT(referral[Assigned to])

AND

COUNT(referral[Assigned to])*MAX(factor[Factor]) ``````

relationship bw Assigned to and  Health care provide

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.