Skip to main content
cancel
Showing results for 
Search instead 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

Reply
CiuCiCiao
Helper I
Helper I

Exclude only one value from filtering

Hi Guys,

I have 2 linked tables.

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

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

 

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

FTE
OfficePotential Hours
Office 11200
Office 21500
Office 32500

 

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

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
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

 

q6.PNG

 

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.

View solution in original post

1 REPLY 1
v-qiuyu-msft
Community Support
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

 

q6.PNG

 

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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