Solved: Subtracting lists not working as expected - Page 2

1minManager · 09-03-2020 09:23 AM

I’m using the following formula to create an ENUM column dependant on items in two seperate tables

Split(
LIST(Select(Labour[Task],AND([Category]=[_ThisRow].[Category],[Bickleigh mid sz]>0)))
-
LIST(Select(TimesheetCompleted[Task],AND(
[Plot]=[_ThisRow].[Plot],
[Category]=[_ThisRow].[Category],
[TimesheetCompletedID]<>[_ThisRow].[TimesheetCompletedID],
[Number Remaining]=0
)))
,",")

I’ve tried to test it via two virtual columns. The top LIST() equation gives
A,B,C,D
and the bottom
A
When I create a 3rd Virtual Column of [VCA]-[VCB] is get
B,C,D which is correct

Yet the main equation stubbonly always shows
A,B,C,D

Must be some sytax error somewhere. I’ve tried (List(A)-List(B)) and LIST(List(A)-List(B)) but its almost like its ignoring List(B)… Any suggestions?

Steve

Remove LIST() from around SELECT(). Remove SPLIT() altogether.

Select(
  Labour[Task],
  AND(
    [Category]=[_ThisRow].[Category],
    [Bickleigh mid sz]>0
  )
)
-
Select(
  TimesheetCompleted[Task],
  AND(
    [Plot]=[_ThisRow].[Plot],
    [Category]=[_ThisRow].[Category],
    [TimesheetCompletedID]<>[_ThisRow].[TimesheetCompletedID],
    [Number Remaining]=0
  )
)

View solution in original post