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! Go to 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
)
)
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:
User | Count |
---|---|
61 | |
25 | |
14 | |
11 | |
6 |