Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I need some help with creating a measure that will calculate an average over a category. I'm creating a report for a hospital to find out whether our depression patients are improving. To do that, we use a standardized depression screening questionnaire (the PHQ-9) and see if the score increases (more depressed) or decreases (less depressed) over time. I want to compare discharge departments to find out which ones' patients are showing the most improvement on average. I also want to exclude patients who only had 1 screening. We can't measure change without multiple scores, and we don't want those patients to water down the average.
Here's some sample data: (NOTE: this data is purely fictional and does not represent information about real people)
Patient ID | Patient Name | PHQ-9 Score | Discharge Department | PHQ-9 Recorded Time |
1 | ONE, PATIENT | 25 | Sample Clinic | 1/1/2015 |
2 | TWO, PATIENT | 26 | Sample Clinic | 1/2/2016 |
2 | TWO, PATIENT | 7 | Sample Clinic | 1/3/2017 |
2 | TWO, PATIENT | 8 | Sample Clinic | 1/4/2018 |
3 | THREE, PATIENT | 20 | Sample Clinic | 1/5/2012 |
3 | THREE, PATIENT | 25 | Sample Clinic | 1/6/2014 |
3 | THREE, PATIENT | 7 | Sample Clinic | 1/7/2017 |
4 | FOUR, PATIENT | 20 | Sample Clinic | 1/8/2018 |
4 | FOUR, PATIENT | 23 | Sample Clinic | 1/9/2015 |
5 | FIVE, PATIENT | 26 | Sample Clinic | 1/10/2016 |
5 | FIVE, PATIENT | 7 | Sample Clinic | 1/11/2018 |
I made a matrix that shows some measures for each patient and for each department. The extracted data is below:
Department/Patient | PHQ-9 Count | Average PHQ-9 Score | First PHQ-9 Value | Last PHQ-9 Value | PHQ-9 Score Overall Change | Average PHQ-9 Change per Disch Dept |
Sample Clinic | 11 | 18.82 | 20 | 7 | -13 | -13 |
ONE, PATIENT | 1 | 25 | 25 | 25 | ||
TWO, PATIENT | 3 | 13.7 | 26 | 8 | -18 | -18 |
THREE, PATIENT | 3 | 17.4 | 20 | 7 | -13 | -13 |
FOUR, PATIENT | 2 | 21.5 | 20 | 23 | 3 | 3 |
FIVE, PATIENT | 2 | 16.5 | 26 | 7 | -19 | -19 |
Everything looks correct except the Average PHQ-9 Change per Discharge Department. It shows -13 for Sample Clinic, but I think it should should be -11.75 (-18 + -13 + 3 + -19 = -47 ÷ 4 = -11.75.) It looks like PBI is calculating the average change for the department the same way a single patient's change is calculated, i.e. the difference between the last and first recorded PHQ-9 values: 8 - 21 = -13 ÷ 1 = -13. How do I rewrite the measure to show the value I'm looking for?
Here's my DAX:
First PHQ-9 Value = SUMX( FILTER( 'PHQ-9 All', 'PHQ-9 All'[PHQ-9 Recorded Time] = MIN( 'PHQ-9 All'[PHQ-9 Recorded Time] ) ), 'PHQ-9 All'[PHQ-9 Score] )
Last PHQ-9 Value = SUMX( FILTER( 'PHQ-9 All', 'PHQ-9 All'[PHQ-9 Recorded Time] = MAX( 'PHQ-9 All'[PHQ-9 Recorded Time] ) ), 'PHQ-9 All'[PHQ-9 Score] )
PHQ-9 Score Overall Change = CALCULATE( [Last PHQ-9 Value] - [First PHQ-9 Value], FILTER( 'PHQ-9 All', COUNTA( 'PHQ-9 All'[Patient ID] ) > 1 //Limit the query to patients who had multiple PHQ-9 scores. ) )
Average PHQ-9 Change per Disch Dept = AVERAGEX( VALUES('PHQ-9 All'[Discharge Department]), //For each discharge department CALCULATE( [PHQ-9 Score Overall Change], //Find the difference between the last and first PHQ-9 scores for each patient. FILTER( 'PHQ-9 All', COUNTA( 'PHQ-9 All'[Patient ID] ) > 1 //Limit the query to patients who had multiple PHQ-9 scores. ) ) )
Thank you in advance!
Solved! Go to Solution.
Hi @SQLguy,
AFAIK, if you measure contains complex filters or conditions based on current row contents, it probably not works on total level.
I'd like to suggest you take a look at following links about how to handle measure formula calculate on hierarchy total level:
Clever Hierarchy Handling in DAX
Measure Totals, The Final Word
I think you need to add conditions to check current row contents to ensure which hierarchy level is, then write measure formulas for correspond total level calculation.
Regards,
Xiaoxin Sheng
Hi @SQLguy , May i ask how did you calculate the Average PHQ-9 Score? i have a similar situation where there are multiple case ID's & score and i want to show a distinct on list of ID's with an Average score. Your Average PHQ-9 Score calc should work for me 🙂
Thanks in advance
Chris
Hi @Anonymous, the measure calculation is simply
Average PHQ-9 Score = AVERAGE( 'PHQ-9 All'[PHQ-9 Score] )
The granularity of the table is one row per office visit, so there may be more than one row for each patient ID. The implicit CALCULATE() in the measure handles the context transition. For example, if I create a table that has unique patient IDs in one column and [Average PHQ-9 Score] in another, the measure will calculate for each row the average of the [PHQ-9 Score] column for that patient's visits only. Does that answer your question?
Hi @SQLguy,
AFAIK, if you measure contains complex filters or conditions based on current row contents, it probably not works on total level.
I'd like to suggest you take a look at following links about how to handle measure formula calculate on hierarchy total level:
Clever Hierarchy Handling in DAX
Measure Totals, The Final Word
I think you need to add conditions to check current row contents to ensure which hierarchy level is, then write measure formulas for correspond total level calculation.
Regards,
Xiaoxin Sheng
Thank you very much for your helpful links! I followed the instructions in the link "Measure Totals, The Final Word", and that worked! I want to make sure that I understand why it worked, though. Can you tell me if this is correct?
Problem: I was trying to do two different calculations in the same column, one for each level in the hierarchy.
Solution: Calculate the measure in two steps.
Avg PHQ-9 Change = //Create a new summary table using the PHQ-9 scores fact table. Group by department //then by patient. Subtract the last score from the first score for each patient. //Include only patients with more than 1 score. VAR _table = SUMMARIZE( 'PHQ-9 All', 'PHQ-9 All'[Discharge Department], 'PHQ-9 All'[Patient Name], "PHQ-9 Score Change", [PHQ-9 Score Overall Change] ) RETURN //Only show the average for the department level. IF( HASONEVALUE( 'PHQ-9 All'[Patient Name] ), BLANK(), AVERAGEX( _table, [PHQ-9 Score Change] ) )
The result of '_table' looks like this:
Discharge Department | Patient Name | PHQ-9 Score Change |
Sample Clinic | ONE, PATIENT | |
Sample Clinic | TWO, PATIENT | -18 |
Sample Clinic | THREE, PATIENT | -13 |
Sample Clinic | FOUR, PATIENT | 3 |
Sample Clinic | FIVE, PATIENT | -19 |
The new matrix looks like this:
Department / Patient | PHQ-9 Count | Average PHQ-9 Score | First PHQ-9 Value | Last PHQ-9 Value | PHQ-9 Score Overall Change | Avg PHQ-9 Change |
Sample Clinic | 11 | 18.82 | 20 | 7 | -13 | -11.75 |
ONE, PATIENT | 1 | 25 | 25 | 25 | ||
TWO, PATIENT | 3 | 13.7 | 26 | 8 | -18 | |
THREE, PATIENT | 3 | 17.4 | 20 | 7 | -13 | |
FOUR, PATIENT | 2 | 21.5 | 20 | 23 | 3 | |
FIVE, PATIENT | 2 | 16.5 | 26 | 7 | -19 |
Hi @SQLguy,
Actually, measure formula not directly calculated on summarize total level which displayed, it will drill to detail records and calculate on them instead of direct calculated on summarize result.
In addition, dax functions which you used to get current row contents will also affected by different hierarchy level.
For instance:
Single row level: max, min... functions are calculated on one row, so they can used to get current row.
Total level(row contents contains multiple records): above methods will calculate on current rows contents and get summarize result instead get list of values.
When you create variable table with summarize result, row content has been limited on single row level(for each calculation) and fixed as a static column value.
So you can get correct summarize result based on static single row measure calculation result.
Hope above helps.
Regards,
Xiaoxin Sheng
Hi,
What is the logic for -18 + -13 + 3 + -19. Why are you excluding the other numebrs from the "PHQ-9 Score Overall Change" column? Please explain.
The calculation should exclude patients who only had 1 PHQ-9 screening. Patient One had only one screening, so we cannot know whether his depression got better or worse. If we include those patients in the average, they will pull the average down.
I changed my DAX formula to use the FILTER logic below. Is this the correct way to filter the query to only patients who had more than one screening? Please let me know if there is a better one.
FILTER( 'PHQ-9 All', COUNTA( 'PHQ-9 All'[Patient ID] ) > 1 //Limit the query to patients who had multiple PHQ-9 scores. )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |