Calculating 'In' and 'Out' Records

[garynorcrossmmc]

Hi all,

I have a data table that looks like this:

PERSONID	EFFFROMDATE	EFFTODATE	TNFR_CC_CHANGE_IN	DIM_FROM_RC_KEY	DIM_RC_KEY
1003110	3/1/2018	3/31/2018	1	67400	68211
1003110	8/1/2020	8/1/2020	1	68211	70515

Each record with a value of 1 for the TNFR_CC_CHANGE_IN field represents an employee transfer.  The request from the user base is identify where there is a transfer in and a transfer out (2 separate measures) based on the selected DIM_RC_KEY value.  So, if a user filtered the report on DIM_RC_KEY = 70515 (or any attributes related to this value), the 'Transfer In' measure should show a value of 1.  If a user filtered the report on DIM_RC_KEY = 68211, the 'Transfer Out' measure should show a value of 1.  

Is this possible to achieve through 2 measures?

Thanks in advance!

[amitchandak · Accepted solution]

@garynorcrossmmc , The two columns should join with a common dimension DIM_RC and there will be one inactive join, that can be activated using userelationship

example

CALCULATE(
SUM('Table'[personID]),

USERELATIONSHIP('DIM_RC'[DIM_RC_KEY],'Table'[DIM_FROM_RC_KEY])
)

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...