Forum Discussion

CB's avatar
CB
Frequent Visitor
8 years ago
Solved

Measure not calculating on broader context/level

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 (measu...
  • CB's avatar
    CB
    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.