Subtracting lists not working as expected

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?

Solved Solved
0 5 585
1 ACCEPTED SOLUTION

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

5 REPLIES 5

Drop the split() - not sure why you have it.

If I don’t put the split then the dropdown only gives me the option of

A,B,C,D

i.e. one choice, rather than

A
B
C
D

4 choices

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
  )
)

Thanks @Steve removing just the 2 x List()'s worked. But I’ve removed the Split() too. I thought that the minus had to be done on 2 items fomated as lists.

List subtraction works with two lists. SELECT() produces a list inherently (as do FILTER() and REF_ROWS()).

See Constructing a List with Subtraction here:

Top Labels in this Space