cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Exclude only one value from filtering

Hi Guys,

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```

Thanks

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors