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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SQLguy
Advocate I
Advocate I

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 Visits tableED Visits table

 

Patients tablePatients 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!VisualsVisuals

 

1 ACCEPTED SOLUTION

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

View solution in original post

13 REPLIES 13
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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

PMPM is showing the total of  all patients' ED charges.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:

  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. 

Added a Diagnosis column and a few more rowsAdded 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.Only patients 1 and 2 had visits for chest pain.

 

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

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?

Yep, drop box or onedrive would both work.

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
Continued Contributor
Continued Contributor

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

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
Continued Contributor
Continued Contributor

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 )

Hi @Iamnvt ,

Thank you for the suggestion. The code you provided does allow users to filter on all columns in the Patients table. However, it does not prevent the user from changing the calculation of TotalCharges by filtering the ED Visits table. I came across some interesting information about how the CALCULATE function works which I think could be helpful. This is from DAX Guide: https://dax.guide/calculate/ Note especially steps 3 and 4. 

When TotalCharges is evaluated in my formula, CALCULATE performs context transition on [Patient MRN] before applying ALLEXCEPT. That cancels out the filter on [Patient MRN]. The result is simply the sum of all the charges in the ED Visits table. I want to do the opposite: First remove any filters on the ED Visits table, then perform context transition to get the sum of charges per patient. Can this be done? If so, how?

CALCULATE evaluation follow these steps:

  1. CALCULATE evaluates all the explicit filter arguments in the original evaluation context. This includes both the original row contexts (if any) and the original filter context. All explicit filter arguments are evaluated independently in the original evaluation context. Once this evaluation is finished, CALCULATE starts building the new filter context.
  2. CALCULATE makes a copy of the original filter context to prepare the new filter context. It discards the original row contexts, because the new evaluation context will not contain any row context.
  3. CALCULATE performs the context transition. It uses the current value of columns in the original row contexts to provide a filter with a unique value for all the columns currently being iterated in the original row contexts. This filter may or may not contain one individual row. There is no guarantee that the new filter context contains a single row at this point. If there are no row contexts active, this step is skipped. Once all implicit filters created by the context transition are applied to the new filter context, CALCULATE moves on to the next step.
  4. CALCULATE evaluates the CALCULATE modifiers used in filter arguments: USERELATIONSHIP, CROSSFILTER, ALL, ALLEXCEPT, ALLSELECTED, and ALLNOBLANKROW. This step happens after step 3. This is very important, because it means that one can remove the effects of the context transition by usingALLas a filter argument. The CALCULATE modifiers are applied after the context transition, so they can alter the effects of the context transition.
  5. CALCULATE evaluates all the explicit filter arguments in the original filter context. It applies their result to the new filter context generated after step 4. These filter arguments are applied to the new filter context once the context transition has happened so they can override it, after filter removal – their filter is not removed by any ALLxxx modifier – and after the relationship architecture has been updated. However, the evaluation of filter arguments happens in the original filter context and it is not affected by any other modifier or filter within the same CALCULATE function.

The filter context generated after point (5) is the new filter context used by CALCULATE in the evaluation of its expression.

Iamnvt
Continued Contributor
Continued Contributor

hi,

 

this one would work for that scenarios:

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

Hi @Iamnvt ,

Thank you again for your suggestion. I tried this code, but unfortunately it gave me an error in the ALLEXCEPT function: "Cannot find table 'Patients'". I think the tables in the first and second arguments of ALLEXCEPT have to be the same. I don't think I can use 'ED Visits' in one and 'Patients' in the other.

Also, VAR TotalEDPatients must use the [Patient MRN] column in 'ED Visits' so that the patient count can still be affected by filters on 'ED Visits'. (Adding CROSSFILTER Both would also work, I think.) For example, say I want to know the PMPM for patients who came to the ED for chest pain. I add a filter on 'ED Visits'[Diagnosis] = "Chest pain". TotalPatients should count only patients who had a visit for chest pain (keep the filter), but TotalCharges should sum all of their ED visits' charges, not just their visits for chest pain (ignore the filter).

I am thinking more and more that this is something that can only be accomplished in calculated columns or tables. That's unfortunate, since I will lose the flexibility of a measure. But if that's the only way to control the calculation of TotalCharges, then that's what I'll do.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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