Reduce "Valid If" List

[Open_YourEyes]

Hey guys,
my column looks like this:

Column name: “Worker2”
Type: “Name”
Valid If: “SELECT(Personal[Name], [Role]=“Worker”)”

So task is, to reduce the list of names, where I get with my “valid if”, minus the value of the column
“Worker1” so that I can’t select a worker twice.

Is this possible? How I can solve this task?
Any ideas?

[Bahbus]

I believe this should work:

SELECT(Personal[Name], [Role]=“Worker”)-SELECT(Personal[Name], [Name]=[_THISROW].[Worker1])

[Marc_Dillon]

Is the ‘Personal’ table different from this one with the [worker1] and [worker2] columns?

I believe you can use an AND expression inside of your select to add a check against the [worker1] column. Such as:

[worker2] valid_if = SELECT(Personal[Name], AND( [Role]=“Worker” , [Name] <> [_THISROW].[Worker1] ) )

[Open_YourEyes]

Marc_Dillon:

[Name] <> [_THISROW].[Worker1] )

@Marc_Dillon its working perfectly!!! Thank you very much!

[Bahbus]

I believe @Marc_Dillon’s method will do the exact same thing more elegantly (and probably faster) than mine.

[Steve]

Try:

SELECT(Personal[Name], [Role]=“Worker”) - LIST([Worker1])

[Bahbus]