Less than/more than table filter returning rows out of bounds

[Wallace_Service]

Hi there! I’m working on a less than/more than filter for a slice of data. It works to some extent but will return results outside of the parameters of the filter. My equations look like this:

AND(

IF(ISBLANK(ANY(Dashboard Filter[CalendarFilterBeforeDate])), true, ISNOTBLANK(SELECT([Related Calendar Events][Connected Case],DATETIME([Start])<=DATETIME(SELECT(Dashboard Filter[CalendarFilterBeforeDate],true)),true))),

IF(ISBLANK(ANY(Dashboard Filter[CalendarFilterAfterDate])), true, ISNOTBLANK(SELECT([Related Calendar Events][Connected Case],DATETIME([Start])>=DATETIME(SELECT(Dashboard Filter[CalendarFilterAfterDate],true)),true)))

)

What I’m trying to do is return the entire slice if both the [CalendarFilterBeforeDate] and [CalendarFilterAfterDate] are blank. If one or both are not blank, then return the rows with related calendar events with [Start] datetime before or after values in the Dashboard Filter columns.

If I use the filters independently it appears to work fine. But if I put values in both the [CalendarFilterBeforeDate] and [CalendarFilterAfterDate], the I’m returned slice values that aren’t between the two column values.

Any suggestions as to what I’m doing wrong in my equations?

Thanks in advance!

Justin

[1minManager]

There’s a lot to say here

ISBLANK(ANY(Dashboard Filter[CalendarFilterBeforeDate]))

Basically says choose any single row and see if this is column is blank. So you need to make sure that there is only 1 row in the Dashboard Filter table

Next you have:
DATETIME([Start])...
and
DATETIME(SELECT(...
Which implies these aren’t DATETIME’s but your forcing them to be. Not sure why your not just using the date. Since I assume that Dashboard Filter[CalendarFilterBeforeDate] is a Date

Finally you have:
ISNOTBLANK(SELECT([Related...
In which your asking if a list is blank. This can sometimes give unusual results. Try this instead:
COUNT(SELECT([Related ...)=0

Simon@1minManager.com

[Rafael_ANEIC-PY]

Hello @Wallace_Service, to add to what Simon said, there’s also:

Wallace_Service:

SELECT(Dashboard Filter[CalendarFilterAfterDate],true)

You could just remove the SELECT() since you’re returning all rows anyways.

I have attempted to simplify your expression with the following, please read the considerations I made afterwards:

IF(
AND(
ISBLANK(Dashboard Filter[CalendarFilterBeforeDate]),
ISBLANK(Dashboard Filter[CalendarFilterAfterDate])),
TRUE,
COUNT(
SELECT(
Dashboard Filter[Related Calendar Events][Connected Case],
AND(
[Start]<=[calendarRef].[CalendarFilterBeforeDate],
[Start]>=[calendarRef].[CalendarFilterAfterDate])
)=0)
)

CONSIDERATIONS

1- Since you’re checking for related calendar events, there must be a reference back to the parent table, replace [calendarRef] with that column’s name.

2- Using the <= and >= operators for comparing dates that might or might not be blank yields the correct logic result (Not sure if this actually happens)

3- The [Start] , [CalendarFilterBeforeDate] and [CalendarFilterBeforeDate] columns are correctly configured as the same date setting.

[Wallace_Service]

Thanks @Rafael_ANEIC-PY and @1minManager!

I think we’re getting closer. One other consideration is that the Dashboard Filter is a slice of the “Users” table which filters the “Case Info” table. The [Start] column is part of the Google Calendar table. So, essentially I’m using the dashboard filter to filter related Google Calendar entries to only show Case Info rows between certain calendar dates.

Does this make any difference to the equation above. I think the logic works but I’m not sure if my references are correct. I’m still getting some invalid filters errors.

[Wallace_Service]

Hi guys, thanks for the help so far. I’ve having a hard time getting this going still. Here’s what I have…

IF(
AND(
ISBLANK(Dashboard Filter[CalendarFilterBeforeDate]),
ISBLANK(Dashboard Filter[CalendarFilterAfterDate])),
TRUE,
COUNT(
SELECT(
Case Info Database[Related Calendar Events][Connected Case],
AND(
[CalendarRef].[Start]<=[CalendarFilterRef].[CalendarFilterBeforeDate],
[CalendarRef].[Start]>=[CalendarFilterRef].[CalendarFilterAfterDate])
)=0)
)

There is something wrong with my SELECT statement. I’m trying to return Case Info Database rows with Related Calendar events between the CalendarFilterBeforeDate and CalendarFilterAfterDate. [CalendarFilterBeforeDate] and [CalendarFilterAfterDate] are part of the Users tabel. The [Start] column is part of the Google Calendar table. The [CalendarRef] is a Ref column in the Case Info Database pointing to the Google Calendar table. The [CalendarFilterRef] column is a Ref column in the Case Info Database pointing to the Users table.

I’m getting an invalid filter condition error when saving but I can’t see the issue as this statement is part of a bigger AND statement and the actually issue cuts off the error message.

Any ideas? Thanks in advance!

[Steve]

Try this:

ISNOTBLANK(
  SELECT(
    [Related Calendar Events][_ROWNUMBER],
    AND(
      ISNOTBLANK([Start]),
      OR(
        ISBLANK(Dashboard Filter[CalendarFilterAfterDate]),
        (DATE([Start]) >= ANY(Dashboard Filter[CalendarFilterAfterDate]))
      ),
      OR(
        ISBLANK(Dashboard Filter[CalendarFilterBeforeDate]),
        (DATE([Start]) <= ANY(Dashboard Filter[CalendarFilterBeforeDate]))
      )
    )
  )
)