Forum Discussion
USERELATIONSHIP usage: One active and one inactive relationship in calculate measure
Hi - I am looking to understand if I need to be using 'USERELATIONSHIP' in my measure and if not, why not?
I am calcualting headcount as at the end of each calendar month. Here is my model:
Active relationship: Leave Date and Date
Inactive relationship: Start Date and Date
Here is my measure:
Headcount End of Month =
CALCULATE (
DISTINCTCOUNT( 'Headcount'[Employee Code] ),
'Headcount'[Start Date] <= MAX ( 'Date Table'[End of Month] )
&& (
Headcount[Leave Date] > MAX ( 'Date Table'[End of Month] )
|| ISBLANK ( Headcount[Leave Date] )
)
)If both relationships are inactive, I am aware that I do not need USERELATIONSHIP.
2 Replies
ibrewop23 , Try like
Headcount End of Month =
CALCULATE (
Countx(Filter( 'Headcount',
'Headcount'[Start Date] <= MAX ( 'Date Table'[End of Month] )
&& (
Headcount[Leave Date] > MAX ( 'Date Table'[End of Month] )
|| ISBLANK ( Headcount[Leave Date] )
)
),'Headcount'[Employee Code] ), crossfilter('Date Table'[Date],'Headcount'[Leave Date], none) )Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970thanks! appreciate you wrting back.
Maybe I should have been clearer but I am looking to find out is an answer to this question specifically:
I am looking to understand if I need to be using 'USERELATIONSHIP' in my measure and if not, why not?
