Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am trying to create a measure that calculates the total count of hires within a specified date range, but does not include hires on the minimum date. I realize this might be a strange requirement, but it is what the user wants.
Right now, I am using this measure:
## - Hire Count (Regular) = CALCULATE( COUNT('HR FACT_WORKFORCEEVENT'[PERSONID]), or('HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Active",'HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Leave With Pay"), 'HR DIM_EMPLOYMENT'[EMPLOYMENTTYPE]="Regular", 'HR FACT_WORKFORCEEVENT'[HIRECOUNT]=1, 'HR FACT_WORKFORCEEVENT'[ACQUISITION]=0, FILTER(ALL('HR FACT_WORKFORCEEVENT'),'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]<=max('Date'[Date]) && 'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]>minx('Date','Date'[Date])) ) |
Solved! Go to Solution.
@garynorcrossmmc , Try with this small change
## - Hire Count (Regular) =
CALCULATE(
COUNT('HR FACT_WORKFORCEEVENT'[PERSONID]),
or('HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Active",'HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Leave With Pay"),
'HR DIM_EMPLOYMENT'[EMPLOYMENTTYPE]="Regular",
'HR FACT_WORKFORCEEVENT'[HIRECOUNT]=1,
'HR FACT_WORKFORCEEVENT'[ACQUISITION]=0,
FILTER(ALL('HR FACT_WORKFORCEEVENT'),'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]<=max('Date'[Date]) &&
'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]>Min('Date'[Date]))
)
/// Do you have to date , then try like
## - Hire Count (Regular) =
CALCULATE(
COUNT('HR FACT_WORKFORCEEVENT'[PERSONID]),
or('HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Active",'HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Leave With Pay"),
'HR DIM_EMPLOYMENT'[EMPLOYMENTTYPE]="Regular",
'HR FACT_WORKFORCEEVENT'[HIRECOUNT]=1,
'HR FACT_WORKFORCEEVENT'[ACQUISITION]=0,
FILTER(ALL('HR FACT_WORKFORCEEVENT'),'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]<=max('Date'[Date]) &&
'HR FACT_WORKFORCEEVENT'[EFFToDATE]>max('Date'[Date]))
)
Hi, @garynorcrossmmc
Has amitchandak’s reply helped you to successfully solve this problem?
If so, would you like to mark your own reply as a solution so that others can learn from it too?
Another important point you may dismiss is that you should make sure the date table doesn’t have a relationship with the main table, it may cause problems if you used the ‘Date’[Date] as the column in the slicer.
Thanks in advance!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@garynorcrossmmc , Try with this small change
## - Hire Count (Regular) =
CALCULATE(
COUNT('HR FACT_WORKFORCEEVENT'[PERSONID]),
or('HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Active",'HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Leave With Pay"),
'HR DIM_EMPLOYMENT'[EMPLOYMENTTYPE]="Regular",
'HR FACT_WORKFORCEEVENT'[HIRECOUNT]=1,
'HR FACT_WORKFORCEEVENT'[ACQUISITION]=0,
FILTER(ALL('HR FACT_WORKFORCEEVENT'),'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]<=max('Date'[Date]) &&
'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]>Min('Date'[Date]))
)
/// Do you have to date , then try like
## - Hire Count (Regular) =
CALCULATE(
COUNT('HR FACT_WORKFORCEEVENT'[PERSONID]),
or('HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Active",'HR DIM_EMPLOYMENT'[EMPLOYMENTSUBSTATUS]="Leave With Pay"),
'HR DIM_EMPLOYMENT'[EMPLOYMENTTYPE]="Regular",
'HR FACT_WORKFORCEEVENT'[HIRECOUNT]=1,
'HR FACT_WORKFORCEEVENT'[ACQUISITION]=0,
FILTER(ALL('HR FACT_WORKFORCEEVENT'),'HR FACT_WORKFORCEEVENT'[EFFFROMDATE]<=max('Date'[Date]) &&
'HR FACT_WORKFORCEEVENT'[EFFToDATE]>max('Date'[Date]))
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |