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 II

## Filtering a measure

Hi All,

I have a measure which calculates the 12 month rolling headcount from an employee file. Measure as below.

However, i need it to exclude certain "Contract Types" so i have a field called Contract Type, and i need it to count just Perm and Fix term and exclude Agency etc. How would i amend this measure to do that? Thanks in advance for any suggestions!

Rolling 12 month HC =
CALCULATE(
COUNT( 'HC'[Employee ID] ),
DATESINPERIOD( 'Date'[Date], MAX( 'Date'[Date] ), -12, MONTH ))
1 ACCEPTED SOLUTION
Community Champion

Hi @PlentyL ,

You can try like this:-

``````Rolling 12 month HC =
CALCULATE (
COUNT ( 'HC'[Employee ID] ),
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ),
FILTER (
'table',
table[Contract Type]
IN { "Perm", "Fix" }
&& table[Contract Type] <> Agency
)
)``````

Thanks,

Samarth

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

2 REPLIES 2
Helper II

It didnt work with the "&& table[contract type] <> Agency line

But has worked with just the IN line. Thank you!!

Community Champion

Hi @PlentyL ,

You can try like this:-

``````Rolling 12 month HC =
CALCULATE (
COUNT ( 'HC'[Employee ID] ),
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ),
FILTER (
'table',
table[Contract Type]
IN { "Perm", "Fix" }
&& table[Contract Type] <> Agency
)
)``````

Thanks,

Samarth

Best Regards,
Samarth

If this post helps, please consider accepting 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.