Forum Discussion
Measure not calculating on broader context/level
- 8 years ago
I was able to resolve the issues by using the ALLEXCEPT with 2 columns/slicers. See the red highlighted lines. I used the allexcept in both the filter and in the actual calculation. For the calculation I wrapped it in a calculate so we can change the row context to use just the context of selecte dates and patient vs. the facility row context.
var months = AVERAGEX( //for each patient and programstart find the number of months enrolled. SUMMARIZE( 'AllEncounters', 'AllEncounters'[Patient ID]),//,'AllEncounters'[Program Start Date], // "MonthsEnrolled", //look only at records whose program dates are within the date range selected //and they are enrolled at least 1 month. We don't want to include those with no enrollment in the averaging. maxx( FILTER(allexcept('AllEncounters','AllEncounters'[Patient ID],'CalendarTable'[Date]), 'AllEncounters'[Program Start Date] <= 'AllEncounters'[1 DateEnd] && 'AllEncounters'[Program End Date] >= 'AllEncounters'[1 DateStart] && DATEDIFF('AllEncounters'[2 Program Start Date within Date Range], 'AllEncounters'[2 Program End Date Within Date Range], month) > 0 ), //end filter*/ //find the number of months between enrollment dates
calculate( DATEDIFF('AllEncounters'[2 Program Start Date within Date Range],
'AllEncounters'[2 Program End Date Within Date Range], month) ,allexcept('AllEncounters','AllEncounters'[Patient ID],'CalendarTable'[Date])))//end maxx
// ), //end outer summarize
// [MonthsEnrolled]
)//get the average months enrolled
return if(months <> 0,months, blank())
Here is the correct result (10 is showing across the board regardless of the facility:
This also seems to have corrected the calculation error described in "issue 2".The % is calculating correctly.Hope this helps others in the future.
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Hello,
Thank you for your response. I don't see either of these issues being a total related problem.
1) Issue 1 is on each of the facilities level that is not showing the correct values for the measure. The total is actually correct (average).
2) Issue 2 is also on an individual facility level and the total is fine as well.
Here is a link to the PBIX. Please let me know if you have donwloaded it as I don't want to keep it public for long due to the data within. Please look at the "encounters - testing" tab at the above measures when filtering for patient "augustine lee". In the middle visual where you see the breakout of "per month" add facility name and you will see the issue described.