Expression help

[jonbowles84]

Hi Geniuses, I have a slice expression and a VC formula that I could really use a hand with... The slice expression currently is:

AND(
     LOOKUP(USEREMAIL(), "Staff", "Email", "Full Name")=[Name],
     LOOKUP(USEREMAIL(), "Staff", "Email", "FilterFrom")<=[Date],
     LOOKUP(USEREMAIL(), "Staff", "Email", "FilterTo")>=[Date],
     IF(LOOKUP(USEREMAIL(), "Staff", "Email", "FilterProject")="","",LOOKUP(USEREMAIL(), "Staff", "Email", "FilterProject")=[Project])
)

Its the IF statement at the end thats the problem. Basically I want the result to be filtered by Staff[FilterProject] if it is not empty, if it is empty I want not include it in the filter and just display all projects.
The VC formula is way beyond me! This is what I have so far:

IF(AND(
	LOOKUP(USEREMAIL(), "Staff", "Email", "Full Name")=[Name],
	COUNT(Timesheet[ID])>1,[Start Time]=[Finish Time],"7:00AM"))

Basically I want the [start time] of my timesheet to match [finish time] of the last entry if there is one by the logged in user...
Any help would be spectacular!!

[Joseph_Seddik]

Hello,

It is very hard trying to understand what are you trying to do, and couldn't find a meaning in the expressions. It is better just to show us the columns that you have in your tables please and then explain the result you want to reach. Thank you.

[jonbowles84]

Well the slice expression is a follow on from this question, I have 3 filter columns that are specific to the user in the "Staff" table that I want to use to filter the data in the "Timesheet" table:

1. Staff[FilterFrom] to filter [Date]
2. Staff[FilterTo] to filter [Date]
3. Staff[FilterProject] to filter [Project]

I can filter easy enough using this:

AND(
     LOOKUP(USEREMAIL(), "Staff", "Email", "Full Name")=[Name],
     LOOKUP(USEREMAIL(), "Staff", "Email", "FilterFrom")<=[Date],
     LOOKUP(USEREMAIL(), "Staff", "Email", "FilterTo")>=[Date],
     LOOKUP(USEREMAIL(), "Staff", "Email", "FilterProject")=[Project])
)

My problem is how to accept a filter where [FilterProject] is empty to include all projects.

***EDIT***

Ive now tried this, which I cant see why, but it still does not work...

IF(LOOKUP(USEREMAIL(), "Staff", "Email", "FilterProject")="",
AND(
     LOOKUP(USEREMAIL(), "Staff", "Email", "Full Name")=[Name],
     LOOKUP(USEREMAIL(), "Staff", "Email", "FilterFrom")<=[Date],
     LOOKUP(USEREMAIL(), "Staff", "Email", "FilterTo")>=[Date]),
AND(
     LOOKUP(USEREMAIL(), "Staff", "Email", "Full Name")=[Name],
     LOOKUP(USEREMAIL(), "Staff", "Email", "FilterFrom")<=[Date],
     LOOKUP(USEREMAIL(), "Staff", "Email", "FilterTo")>=[Date],
     LOOKUP(USEREMAIL(), "Staff", "Email", "FilterProject")=[Project])
)

The VC formula relates to the Timesheet table:

Where I want the users [Start Time] to be the same as their [Finish Time] if they have already made an entry TODAY() if not to be "7:00AM".
Does that make sense?

***EDIT***
Just trying something like this now, but still no good...

IF(COUNT(FILTER("Timesheet",([Date] = TODAY())))>1,[_THISROW].[Finish Time],"7:00AM")

[Joseph_Seddik]

---

My problem is how to accept a filter where [FilterProject] is empty to include all projects.

---

To use the IF() statement in a filter, keep in mind that the filter requires a True/False input, so any expression your write in the filter should return True/False values. Thus, in general you can write something like: 

IFS(
  ISBLANK([Project]), TRUE,
  [Project] = "filterProject", TRUE,
  TRUE, FALSE
)

I'm not able to help you with the slice, because I have several doubts:

1. In the slice filter, where do [Name], [Date], [Project] come from? 
2. Are you using the same date for both comparisons with FilterFrom and FilterTo?

For the start time formula I suggest the following:

1. Create a slice of Timesheet table that would dynamically contains the today's records per user. Let's name it "userDailyRecords".  Its row filter formula is:
   
   AND(USEREMAIL() = [Email], TODAY() = [Date])
   
   
2. Now you can use this expression as the Initial Value for your Start Time column:
   
   IF( ISNOTBLANK(userDailyRecords[Finish Time]), 
     MAX(userDailyRecords[Finish Time]),
     "7:00AM"
   )

In this way, your "Start Time" column will be set to the last [FinishTime] today for the current user, otherwise "7:00AM".

[Steve]

Your original slice expression is very inefficient: each LOOKUP() expression performs a full scan of the entire Staff table, which could hurt performance as that table grows. It would be better written as:

ISNOTBLANK(
  FILTER(
    "Staff",
    AND(
      (USEREMAIL() = [Email]),
      ([_THISROW].[Name] = [Full Name]),
      ([_THISROW].[Date] >= [FilterFrom]),
      ([_THISROW].[Date] <= [FilterTo]),
      OR(
        ISBLANK([FilterProject]),
        ([_THISROW].[Project] = [FilterProject])
      )
    )
  )
)