Exclude only one value from filtering

[CiuCiCiao]

Hi Guys,

I have 2 linked tables.

the first has the time in minutes spent for the FTEs for all the services provided:

Time
Services	m	Office
Service 1	206	Office 3
Service 2	932	Office 1
Service 3	510	Office 2
Service 4	331	Office 1
Service 5	23	Office 2
Service 6	1.225	Office 1
Service 7	39	Office 3
…	…	…

the second table has the total potential of hours worked for each Office:

FTE
Office	Potential Hours
Office 1	1200
Office 2	1500
Office 3	2500

My objective is to understand the difference between potential hours and hours really spent on services.

So I got this formula to calculate the effective hours worked:

=
CALCULATE (
    SUM ( Time[m] ),
    FILTER ( FTE, FTE[Office] = EARLIER ( Time[Office] ) )
)
    / 60

Now i have to filter out Service 6 and I am pretty sure there is a smarter way than this:

=
CALCULATE (
    CALCULATE (
        SUM ( Time[m] ),
        Time[Services] = "Service 1",
        Time[Services] = "Service 2",
        Time[Services] = "Service 3"
        ...
    ),
    FILTER ( FTE, FTE[Office] = EARLIER ( Time[Office] ) )
)
    / 60

Waiting for your help.

Thanks

[v-qiuyu-msft · Accepted solution]

Hi @CiuCiCiao,

To exclude Service 6 from the returned value, you can create a measure like below:

Measure = CALCULATE(SUM('Time'[m]),FILTER('Time','Time'[Office]=MAX('FTE'[Office]) && 'Time'[Services]<>"Service 6"))/60

Best Regards,
Qiuyun Yu