Forum Discussion

Gazsim44's avatar
Gazsim44
Helper III
6 years ago
Solved

USERELATIONSHIP - COUNTROWS / Two Date Parameters

Hi, I am trying to set a measure that will countrows using two date parameters and so I can determine the number of employees on the payroll by not counting those that have left and had not yet started at a particular date. In theory I had this setup not taking into account that you are not able to have two date relationships setup with my FACT table per below. Any ideas how I can now amend this measure  so it takes into account that I require a USERELATIONSHIP to enable the relationship between HR[Date Left] & Date[Date]?

 

Current Staff = CALCULATE(COUNTROWS(HR), 
FILTER( VALUES( HR[Date Employment Started]), 
HR[Date Employment Started] <= MAX('Date'[Date])),
FILTER( VALUES(HR[Date Left]), OR(HR[Date Left] >= MIN('Date'[Date]),
ISBLANK(HR[Date Left]))))