Reference Column with Status Change

[DarenJanes]

I can't seem to think of a nice solution for this problem without Duplicate UX and Slices.

Employee Table

ID, NAME, STATUS

Expense Table

ID, EMPID (Employee[ID]), ....

For New Expense Records I only want the ability to choose Active Employees (Easy enough with a slice)

But once Employee gets Terminated, I still want to see Expense Record with Employee Name properly referenced rather than ID. I could use a different Slice, but that's duplication I want to avoid.

So, I'm summary I want a reference column to resolve if already populated. Put your thinking caps on boys and girls!

I'm guessing people use Duplicates, but just want to make sure.

Thanks,

Daren

[graham_howe · Accepted solution]

Set the reference to the Employee Table not the slice. Then in the formula for populating the dropdown (Suggested Values), use the slice.