I need help to write a measure that will ignore all filters except for a single column that I specify. I am using ALLEXCEPT(), but it's not working like I would expect. It works fine in one visual but not in another when that column is filtered. The report is for a hospital that wants to track its per member per month (PMPM) costs for Emergency Department patients. PMPM is used as a benchmark of how much it costs to care for a group of patients over time. My data tables look something like this, with a single-direction relationship from Patients to ED Visits on [Patient MRN]:ED Visits table
("MRN" stands for "medical record number", a unique identifier)
The intent of the measure is to keep the calculation constant for each patient no matter what other filters are put on the ED Visits fact table, e.g. diagnosis code, while allowing the user to filter on particular patients using the 'Patients' dimension table.
I want to show this measure both in a card (the PMPM for all selected patients) and in a table that has one row per patient. In the table, the measure value is correct for each patient. But when I filter on a particular patient using a slicer, the value in the card does not match. Instead, it appears to be using the total ED charges for all patients ( $77,878,185 / 1 / 27 ) = $2,884,377. See screenshot below. Is TotalCharges not being affected by the slicer because the filter is on the [Patient MRN] column in the dimension table rather than the fact table? How can I rewrite the measure to make that work? Changing the table name did not have any effect, neither in TotalEDPatients nor TotalCharges. Thank you very much for your help!Visuals
Just wanted to post a quick update to share the solution that I discovered. I found out that, if you use ALL() in a CALCULATE statement along with other filters, the ALL will be applied first, followed by the other filters. This is exactly the behavior that I wanted. Kudos to Marco Russo and Alberto Ferrari for demonstrating this in their Mastering DAX Video Course. The final code for the measure is below. Table 'ED DC Dates' is a date table for column 'ED Visits'[Discharge Date], which is used to determine how many months to use in the denominator of the PMPM calculation.
ED PMPM =
VAR MinDischargeDate = STARTOFMONTH( FIRSTDATE( 'ED DC Dates'[Date] ) )
VAR MaxDischargeDate = ENDOFMONTH( LASTDATE( 'ED DC Dates'[Date] ) )
VAR TotalCharges = CALCULATE(
SUM( 'ED Visits'[Total Charges] ),
ALL( 'ED Visits' ), // First, clear any existing filters on the 'ED Visits' table...
VALUES( 'ED Visits'[Patient MRN] ), // Then, filter by the patient(s) in the current filter context...
DATESBETWEEN( 'ED Visits'[Discharge Date], MinDischargeDate, MaxDischargeDate ) // Who had ED visits within the selected date range
VAR ChargesPerPatient = CALCULATE( DIVIDE( TotalCharges, DISTINCTCOUNT( 'ED Visits'[Patient MRN] ) ), 'ED Visits'[HAR Total Charges] > 0 ) // Exclude patients who had no charges
VAR Months = DATEDIFF( MinDischargeDate, MaxDischargeDate + 1, MONTH ) // Count the number of months in the current date range
VAR PMPM = DIVIDE ( ChargesPerPatient, Months )
Thank you very much for your reply! Unfortunately, that output isn't exactly what I'm looking for. The PMPM column does not equal (Total Charges / Active Months ). For example, patient #1's PMPM should be (9000 / 27) = 333.33, not 592.59. We can see why this is happening if we change the DAX code to return TotalCharges instead:
PMPM is showing the total of all patients' ED charges.ALLEXCEPT is ignoring the filter on Patient MRN in the visual. Here is what I would like the measure to do:
Remove any filters on the ED Visits table coming from slicers, report filters, or other visuals, BUT still allow cross-filtering from columns in the Patients table, such as 'Patients'[Patient MRN].
Calculate SUM( 'ED Visits'[Total Charges] ).
Divide the total by a specified number of months.
Basically, I just don't want the calculation to change based on anything the user has selected from the ED Visits table. How do I lock that down? I wanted to upload a new version of your pbix file, but I couldn't figure out how. I will just show an image of an updated ED Visits table instead.
Added a Diagnosis column and a few more rowsFor example, I want to know how much it costs per month to take care of patients who came to the ED for chest pain. For each patient, I want the value of the PMPM measure to include the charges for all of that patient's visits, not just their visits for chest pain. Can you help me do that? I would really appreciate it! Thanks again!Only patients 1 and 2 had visits for chest pain.
Thanks for your reply! I would be glad to, but I can't seem to figure out how to do it... There doesn't appear to be a button in the toolbar for attaching a file. Do I need to upload it somewhere else and link to it, like Dropbox or something?
@SQLguy : you should use ALLEXCEPT in Dimension table, not Fact table. Otherwise, when you use Dimension table to in Slicer, the relationship will not transverse filter modififer (allexcept) from Fact table to Dimension table.
Hi @Iamnvt, Thank you very much for the suggestion! I tried using your DAX, but unfortunately it does not give the result I am looking for. It allows filtering on 'Patients'[Patient MRN], but it does not allow filtering on any other column in the 'Patients' table, such as age, sex, etc. In addition, the number will change if the user filters on a column in the 'ED Visits' table, such as [Diagnosis], because the amount of TotalCharges will change. That would not be correct in this case because per member per month costs include all of a patient's visits. I do not want the TotalCharges to change based on any filters placed on the 'ED Visits' table.
I could add ALLs to the CALCULATE in TotalCharges for every 'ED Visits' column that the user might filter on, but that table is a data mart with dozens of columns. It would be a very long list. I thought about adding a calculated column in the 'Patients' table, but I also need to be able to display the overall PMPM for a group of patients. That number can change depending on how many patients and months are selected, so a measure is required. I will work on creating an anonymous data set and post a link.