cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SQLguy Frequent Visitor
Frequent Visitor

ALLEXCEPT works in a table but not in a card

Hi all,

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 data screenshot.pngED Visits table

 

Patient data screenshot.pngPatients table

The code for the PMPM measure is below:

PMPM = 
VAR TotalEDPatients = DISTINCTCOUNT( 'ED Visits'[Patient MRN] )
VAR TotalCharges = CALCULATE( SUM( 'ED Visits'[Visit Charges] ), ALLEXCEPT( 'ED Visits', 'ED Visits'[Patient MRN] ) )
VAR ChargesPerPatient = DIVIDE ( TotalCharges, TotalEDPatients ) )
VAR ActiveMonths = MAX( 'Patients'[Active Months] )
RETURN
DIVIDE ( ChargesPerPatient, ActiveMonths )

("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!PMPM screenshot.pngVisuals

 

1 ACCEPTED SOLUTION

Accepted Solutions
SQLguy Frequent Visitor
Frequent Visitor

Re: ALLEXCEPT works in a table but not in a card

Hi all,

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 )
RETURN PMPM
13 REPLIES 13
Community Support Team
Community Support Team

Re: ALLEXCEPT works in a table but not in a card

Hi @SQLguy ,

Based on your description and your formula, I made a test to reproduce your scenario.

Do you want to get the output like below?

Untitled.png

If it is , you could create the relationship like below and create the table with Patient MRN from the Patient table.

re.PNG

In addition, you could refer to my attachement. If you still need help, please share your desired output so that we could help further on it.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SQLguy Frequent Visitor
Frequent Visitor

Re: ALLEXCEPT works in a table but not in a card

Hi @v-piga-msft ,

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 = 
VAR TotalEDPatients = DISTINCTCOUNT( 'ED Visits'[Patient MRN] )
VAR TotalCharges = CALCULATE( SUM( 'ED Visits'[Total Charges] ), ALLEXCEPT( 'ED Visits', 'ED Visits'[Patient MRN] ) )
VAR ChargesPerPatient = DIVIDE ( TotalCharges, TotalEDPatients ) 
VAR ActiveMonths = MAX( 'Patients'[Active Months] )
RETURN
// DIVIDE ( ChargesPerPatient, ActiveMonths )
TotalCharges

Total charges screenshot.pngPMPM 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:

  1. 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].
  2. Calculate SUM( 'ED Visits'[Total Charges] ).
  3. 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. 

New diagnosis screenshot.pngAdded 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!Diagnosis slicer screenshot.pngOnly patients 1 and 2 had visits for chest pain.

 

Super User
Super User

Re: ALLEXCEPT works in a table but not in a card

@SQLguy can you give us an anonymized copy of your .pbix so that we can see the relationships and test the measures in the actual working environment?

SQLguy Frequent Visitor
Frequent Visitor

Re: ALLEXCEPT works in a table but not in a card

Hi @jdbuchanan71 ,

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?

Super User
Super User

Re: ALLEXCEPT works in a table but not in a card

Yep, drop box or onedrive would both work.

Super User
Super User

Re: ALLEXCEPT works in a table but not in a card

Also @SQLguy since you are working with PMPM stuff you might be interested in a solution I did for PMPM projection based on trending.  

https://community.powerbi.com/t5/Desktop/Average-of-6-month-and-12-month-future-trend/m-p/721657

 

Iamnvt Member
Member

Re: ALLEXCEPT works in a table but not in a card

@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.

 

this measure should work:

PMPM = 
VAR TotalEDPatients = DISTINCTCOUNT( Patients[Paint MRN] )
VAR TotalCharges = CALCULATE( SUM( 'ED Visits'[Total Charges] ), ALLEXCEPT(Patients, Patients[Paint MRN] ) )
VAR ChargesPerPatient = DIVIDE ( TotalCharges, TotalEDPatients ) 
VAR ActiveMonths = MAX( 'Patients'[Active Months] )
RETURN
DIVIDE ( ChargesPerPatient, ActiveMonths )
SQLguy Frequent Visitor
Frequent Visitor

Re: ALLEXCEPT works in a table but not in a card

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.

Iamnvt Member
Member

Re: ALLEXCEPT works in a table but not in a card

if you want to filter on all possible columns, then you don't need ALLEXCEPT, just normal SUM would work:

PMPM = 
VAR TotalEDPatients = DISTINCTCOUNT( Patients[Paint MRN] )
VAR TotalCharges = CALCULATE( SUM( 'ED Visits'[Total Charges] ))
VAR ChargesPerPatient = DIVIDE ( TotalCharges, TotalEDPatients ) 
VAR ActiveMonths = MAX( 'Patients'[Active Months] )
RETURN
DIVIDE ( ChargesPerPatient, ActiveMonths )

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 373 members 4,062 guests
Please welcome our newest community members: