Create a column whose values change depending on the number of instances of ID

[JadeM]

Hi all, I have a dataset that shows the movement of staff between departments. I want to calculate if staff movements are 'internal' or 'external', this changes depending on the filters in place (department, role, location etc).

If a staff ID appears twice within the same filter view the movement is 'Internal'

If a staff ID appears only once in a filter view the movement is 'External'

I want to create a column that will dynamically change between Internal and External depending on the filter view. As an example, a Staff ID may appear twice in the scenario when they are moving from the HR department to the marketing department if there are no filters on the table (one row will show as a move away from HR and a second row will show them moving into marketing). In this scenario they should be marked as INTERNAL (as the staff ID appears twice). But if a user then filters the table to show only the HR deparment, the staff ID will only show once (the staff member leaving the HR department) so the value in the new column needs tro change to EXTERNAL.

Example:

Initial, unfiltered view:

Staff ID	Name	Department	Type of move	Internal or External
1	J Jones	HR	Leaving department	Internal
1	J Jones	Marketing	New to department	Internal
2	T Tim	HR	New joiner	External
3	R Rogers	Development	Retirement	External
4	L Lemon	Marketing	Leaving department	Internal
4	L Lemon	Operations	New to department	Internal
5	H Harris	Operations	Retirement	External
6	N Norman	Security	Retirement	External

Desired view if you filter to show only HR:

Staff ID	Name	Department	Type of move	Internal or External
1	J Jones	HR	Leaving department	External
2	T Tim	HR	New joiner	External

You can see than Staff ID should change from Internal to External if you apply the filter on department (as it goes from only two count of 'Staff ID' 1 to one count

I have been trying to make a calculated column to create this 'Internal or External' column but am not having any luck. Can anyone please help?

This is what I have tried so far (but it does not act dynamically when the table is filtered, it only ever shows Internal):

Internal or External =
VAR CurrentStaffNumber = Table[Staff ID]
VAR EmployeeCount =
    CALCULATE(
        COUNTROWS(Table),
        FILTER(ALL(Table), Table[Staff ID] = EARLIER(Table[Staff ID]))
    )
RETURN
    IF(
        EmployeeCount > 1,
        "Internal",
        "External"
    )

[Igna · Accepted solution]

Hello,

Try to use SELECTEDVALUE() to catch the staff_id. You are right, you must use a measure to make the internal/external dynamic.

Try this : 

Internal or External Measure = 
VAR SelectedStaffID = SELECTEDVALUE(Table[Staff ID])
VAR EmployeeCount =
    CALCULATE(
        COUNTROWS(Table),
        FILTER(ALL(Table), Table[Staff ID] = SelectedStaffID)
    )
RETURN
    IF(
        EmployeeCount > 1,
        "Internal",
        "External"
    )

Igna