Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to Use a variable as filter in caclulate

I am trying to sum all the employees that left between 30 to 365 days.
how can I use the "flag" in the calculate formula?
 
 
# Under 1 year =
var workStartDate=MAX('Employee data for PBI'[Work Start Date])
var workEndDate=MAX('Employee data for PBI'[Final Process Date])
var seniority= DATEDIFF(workStartDate, workEndDate, DAY)
var flag=IF(seniority>=30 && seniority<=365,1,0)
var emps=CALCULATE(SUM('Employee data for PBI'[# Terminated]))
return emps
1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@Anonymous 

 

Try add All() function to set the context to all table:

 

var emps=CALCULATE(SUM('Employee data for PBI'[# Terminated]), filter(All('Employee data for PBI'), flag =1))

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
V-pazhen-msft
Community Support
Community Support

@Anonymous 

 

Try add All() function to set the context to all table:

 

var emps=CALCULATE(SUM('Employee data for PBI'[# Terminated]), filter(All('Employee data for PBI'), flag =1))

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

amitchandak
Super User
Super User

@Anonymous , Try a new measure like

 

# Under 1 year =
var workStartDate=('Employee data for PBI'[Work Start Date])
var workEndDate=('Employee data for PBI'[Final Process Date])
var seniority= DATEDIFF(workStartDate, workEndDate, DAY)
var flag=IF(seniority>=30 && seniority<=365,1,0)
var emps=CALCULATE(SUM('Employee data for PBI'[# Terminated]), filter('Employee data for PBI', flag =1))
return emps

Anonymous
Not applicable

thanks @amitchandak! it looks like it works, but the measure doesn't sum the total number of employees terminated.

if I put the data on a table for each employee I do see the values (0 or 1)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.