Measure not calculating on broader context/level

[CB]

Hello,

I've researched the many community discussions around context and measures extensively but could not figure out a solution. 

Issue:

I have a series of measures built on each other (measures so that the different slicers are applied). The story I'm trying to present is analysis of patients and the number of different types of visits, comparing before a certain point (before enrollment to a  particular program) vs. after that point. Some examples of what is being calculated/measured/displayed are:

- how many visits/month before enrollment (between a date range selected) vs. how many visits/month after enrollment

-  % change between the before and after. 

I have all the measures working properly on a patient level as well as across many different patients (if all are selected in the patient slicer). 

However, if I try to display the measures across facilities (patient visits can be seen in multiple departments ) such as the average count/month before enrollment in each of the facilities, the measure is not calculating correctly even when pulling one single patient. I narrowed this down to the link between the calendar table I use to select the date range which is joined to the visits table (on appointment date to calendar date). For most measures I want this date slicer to be applied so it counts the visits within this date range only. But for some measures such as the number of months of enrollment that is not specific to a particular facility, I don't want the measure to look at the visits of the date range in that particular facility but rather across the board. If patient A is enrolled for 10 months, it is 10 months in every facility whether or not there were visits in that facility during that date range. But because the date range is linked to the visits table it is pulling only the records for that facility in that date range. 

When I take out the facility and have just the patient, it shows the correct 10. 

When I used an ALLEXCEPT for facility name, it worked to show the number of months for all facilities, but it didn't take into account the date range selected - so if the date range was 1/1/18 - 5/1/18 it should show 4 for number of months (for patient, for all facilities). If date range is 1/1/18 - 6/1/18 it should show 5  but it was showing the max available for that patient across all records regardless of the date range ( in this case it was 10 even if the date range spanned much less. 

When I removed the join, the same affect happens - the date range slicer is not taken into account for this measure or any other measure. 

1) So how can I have the measure calculate correctly using the date slicer AND ignore the facility filter/level in the matrix? 

The measure is:

3 Months Enrolled Per Patient Per Program = 
//finds total number of months enrolled within the date range selected per patient per program start. Since it's possible to have more than one set of enrollment dates, we need to find all the months across all enrollments within the date range. This measure finds per patient per program. As of this time, patients have only one set of enrollment dates. 

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('AllEncounters',
                    '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
                DATEDIFF('AllEncounters'[2 Program Start Date within Date Range], 
                         'AllEncounters'[2 Program End Date Within Date Range], month)) 
               ), //end outer summarize
            [MonthsEnrolled]  )//get the average months enrolled
     

return if(months <> 0,months, blank())

This works fine for the patient level. 

2) Another issue with the facility breakout is measures using other measure results don't calculate correctly even when the measures it uses are correct. When I hard code the values, the measure works. 

measure: 

9 % Change Before to After Enrollment (All) = 

//find the percent change from before to after enrollmnent but only for those that have a before and after
IF('AllEncounters'[8 NotEnrolledVisitCountPerMonth]  <> BLANK() && 'AllEncounters'[7 EnrolledVisitCountPerMonth] <> BLANK(),

DIVIDE( 'AllEncounters'[7 EnrolledVisitCountPerMonth]- 'AllEncounters'[8 NotEnrolledVisitCountPerMonth] , 'AllEncounters'[8 NotEnrolledVisitCountPerMonth],0),  //formula is (after - before)/before

BLANK() ) //if before or after is blank , leave as blank. This also ensures no "infinity" occurs if the divisor is blank. 

This uses the "7 EnrolledVisitCountPerMonth]" and "8 NotEnrolledVisitCountPerMonth" measures. Both show correctly even on the facility level. But the final measure does not match the result if you calculate manually (or with hardcoded values):

Example:

"7 EnrolledVisitCountPerMonth" = .63

"8 NotEnrolledVisitCountPerMonth"  = .57

formula is (.63-.57)/.57 = 10.5% 

but here 9.4% is calculating. When I replace the 9b% code with hard coded values  (.63-.57)/.57, it calculates as expected. 

Again, everything is working fine with the various slicers on a per patient level or even on multiple patients (averages are working correctly). But splitting up by facility is not working. 

I know this was a very long post. I really appreciate your help in resolving these anomolies. I'm happy to share a pbix to individuals. 

[Greg_Deckler]

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

[CB]

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. 

[CB]

Hello,

I've researched the many community discussions around context and measures extensively but could not figure out a solution. 

Issue:

I have a series of measures built on each other (measures so that the different slicers are applied). The story I'm trying to present is analysis of patients and the number of different types of visits, comparing before a certain point (before enrollment to a  particular program) vs. after that point. Some examples of what is being calculated/measured/displayed are:

- how many visits/month before enrollment (between a date range selected) vs. how many visits/month after enrollment

-  % change between the before and after. 

I have all the measures working properly on a patient level as well as across many different patients (if all are selected in the patient slicer). 

However, if I try to display the measures across facilities (patient visits can be seen in multiple departments ) such as the average count/month before enrollment in each of the facilities, the measure is not calculating correctly even when pulling one single patient. I narrowed this down to the link between the calendar table I use to select the date range which is joined to the visits table (on appointment date to calendar date). For most measures I want this date slicer to be applied so it counts the visits within this date range only. But for some measures such as the number of months of enrollment that is not specific to a particular facility, I don't want the measure to look at the visits of the date range in that particular facility but rather across the board. If patient A is enrolled for 10 months, it is 10 months in every facility whether or not there were visits in that facility during that date range. But because the date range is linked to the visits table it is pulling only the records for that facility in that date range. 

When I take out the facility and have just the patient, it shows the correct 10. 

When I used an ALLEXCEPT for facility name, it worked to show the number of months for all facilities, but it didn't take into account the date range selected - so if the date range was 1/1/18 - 5/1/18 it should show 4 for number of months (for patient, for all facilities). If date range is 1/1/18 - 6/1/18 it should show 5  but it was showing the max available for that patient across all records regardless of the date range ( in this case it was 10 even if the date range spanned much less. 

When I removed the join, the same affect happens - the date range slicer is not taken into account for this measure or any other measure. 

1) So how can I have the measure calculate correctly using the date slicer AND ignore the facility filter/level in the matrix? 

The measure is:

3 Months Enrolled Per Patient Per Program = 
//finds total number of months enrolled within the date range selected per patient per program start. Since it's possible to have more than one set of enrollment dates, we need to find all the months across all enrollments within the date range. This measure finds per patient per program. As of this time, patients have only one set of enrollment dates. 

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('AllEncounters',
                    '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
                DATEDIFF('AllEncounters'[2 Program Start Date within Date Range], 
                         'AllEncounters'[2 Program End Date Within Date Range], month)) 
               ), //end outer summarize
            [MonthsEnrolled]  )//get the average months enrolled
     

return if(months <> 0,months, blank())

This works fine for the patient level. 

2) Another issue with the facility breakout is measures using other measure results don't calculate correctly even when the measures it uses are correct. When I hard code the values, the measure works. 

measure: 

9 % Change Before to After Enrollment (All) = 

//find the percent change from before to after enrollmnent but only for those that have a before and after
IF('AllEncounters'[8 NotEnrolledVisitCountPerMonth]  <> BLANK() && 'AllEncounters'[7 EnrolledVisitCountPerMonth] <> BLANK(),

DIVIDE( 'AllEncounters'[7 EnrolledVisitCountPerMonth]- 'AllEncounters'[8 NotEnrolledVisitCountPerMonth] , 'AllEncounters'[8 NotEnrolledVisitCountPerMonth],0),  //formula is (after - before)/before

BLANK() ) //if before or after is blank , leave as blank. This also ensures no "infinity" occurs if the divisor is blank. 

This uses the "7 EnrolledVisitCountPerMonth]" and "8 NotEnrolledVisitCountPerMonth" measures. Both show correctly even on the facility level. But the final measure does not match the result if you calculate manually (or with hardcoded values):

Example:

"7 EnrolledVisitCountPerMonth" = .63

"8 NotEnrolledVisitCountPerMonth"  = .57

formula is (.63-.57)/.57 = 10.5% 

but here 9.4% is calculating. When I replace the 9b% code with hard coded values  (.63-.57)/.57, it calculates as expected. 

Again, everything is working fine with the various slicers on a per patient level or even on multiple patients (averages are working correctly). But splitting up by facility is not working. 

I know this was a very long post. I really appreciate your help in resolving these anomolies. I'm happy to share a pbix to individuals. 

[CB · Accepted solution]

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.