Forum Discussion

Banistas's avatar
Banistas
Icon for Helper I rankHelper I
2 years ago
Solved

Filters wrongly changing result in measure

I have a measure that works well up until I add filters to it. However, I don't understand why the filters affect the measure the way they do and I need help figuring it out.  

 

The measure is "FTE to Pay":

FTE to Pay =
DIVIDE ( SUM ( 'Schedule'[Total Scheduled Hours] ), [No. of Week Days] * 8 )
-- The measure calculates the FTE from the total hours 

However, when I add a Calculate with filters the result changes under some scenarios (and it's wrong): 

FTE to Pay (with filters) = 
     CALCULATE( 
         DIVIDE( sum('Schedule'[Total Scheduled Hours]), 
                 [No. of Week Days]*8 
         ), 
      FILTER('Schedule','Schedule'[Shift Date] > DATE(2024,02,01)), -- does not calculate for data older than February 1st, 2024 
      FILTER('Schedule','Schedule'[Termindated?] <> "Terminated") -- stops calculating when the employee has been terminated ) 

-- The measure intends to calculate the FTE from the total hours 

Note that:

No. of week days =
CALCULATE ( COUNTA ( date[week_day_flag] ), date[week_day_flag] = "Y" )
-- The measure counts the number of week days (Monday to Friday) as per column week_day_flag 

 

Also, tables 'Date' and 'Schedule' are linked on Calendar Date in 'Date' and Shift Date in 'Schedule'.

 

One of the scenarios I have identified where the result is wrong is:

The employee works the following schedule:

 

Monday - 8 hours

Tuesday - Day off

Wednesday - 8 hours

Thursday - 8 hours

Friday - 8 hours

Saturday - 8 hours

Sunday - day off  

 

So, the employee is working 40 hours a week, and on a 8-day standard full-time, I should get a result of 1.0, even though the employee is off on a weekday and working instead on Saturday. The measure results are as follows:

FTE to Pay = 1.0   -- correct  

FTE to Pay (with filters) = 1.25   -- wrong

 

I believe the reason why it gives 1.25, is that it only counts 4 weekdays on the calculation with filters instead of 5 days. However, I don't understand why it does that when the filters are not indicating that No. of Week Days should be filtered. 

 

Any help would be greatly appreciated.     

  • Thanks lbendlin. I resolved it by adding the filters to the table in Power Query rather than having the filters in the measure.   

2 Replies