Calculate Percent Of Patients With (Some Metric) By Site in Power BI

Briefly, I am trying to compute a "percent of patients with (some metric) by site" from two related tables, Patient and PatientMetric, using a custom measure in Power BI. I have a solution using Power Pivot in Excel, but the same measure formula is not working with Power BI.

The tables are:

Patient, with columns SiteCode and PatientCode
PatientMetric, with columns PatientCode and MetricCode


The two tables are related in the obvious way:

PatientMetric.PatientCode -> Patient.PatientCode


I've included sample data below.

In Power Pivot, I create a custom measure ...

DISTINCTCOUNT(PatientMetric[PatientCode])/DISTINCTCOUNT([PatientCode])


... and when I create a pivot table with Rows = "SiteCode" (from Patient), Columns = "MetricCode" (from PatientMetric), and Values = (my custom measure), I get what I was hoping to get ...

SiteCode M1 M2
1 67% 67%
2 100% 50%


In Excel, the denominator in the measure is properly filtered by the SiteCode before counting.

However, when I use Power BI, with exactly the same data model and relationships, and build a table, I get the results ...

SiteCode M1 M2
1 40% 40%
2 40% 20%


... because Power BI is using the total patient count as the denominator, unfiltered by the SiteCode.

How can I fix this?

Patient data is ...

+----------+-------------+
| SiteCode | PatientCode |
+----------+-------------+
| 1 | 10 |
| 1 | 11 |
| 1 | 12 |
| 2 | 20 |
| 2 | 21 |
+----------+-------------+


... and PatientMetric data is ...

+-------------+------------+
| PatientCode | MetricCode |
+-------------+------------+
| 10 | M1 |
| 10 | M2 |
| 11 | M1 |
| 12 | M2 |
| 20 | M1 |
| 20 | M2 |
| 21 | M1 |
+-------------+------------+


Patient[PatientCode]

1 Answer
1

I didn't "solve" the problem per se, but after wasting @AlexisOlson's time, I recreated my Power BI slicer and table and got the correct results. I compared the working version to the non-working version and couldn't find the difference, but there are LOTS of settings.

Moral of the story is: try recreating with a stripped down version; Power BI has lots of hidden stuff that can bit you.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.