Check event venue and date and time range

[halmairobb]

I'm stuck for days, please help me. I have an event calendar and i need to compare in the form, whether the given location (column [Venue]) is free at the selected time ([EventStartDate] and [EventEndDate] - both are DateTime column) of a planned event. Every datas are on the same table (EVENTS). The compare of the date range works fine, but i cannot compare properly the [Venue] column, wich type is EnumList.

I have tried a lot of expression, nothing is work properly yet. With the last expression, it seems to me, that it compares just the first element of the list, than stops. So other items are left out of the list. 

This is the closest i've been:

[WillowMobileSys]

---

@halmairobb wrote:

With the last expression, it seems to me, that it compares just the first element of the list, than stops.

---

That is correct!  The expression, INDEX(Events[Venue], 1), will always return the Venue listed in the very first Events row.

It seems, since you are validating that the chosen Venue AND Event Dates, that you can simply use your FILTER() expression for both.  I think this expression would achieve your goal:

COUNT(
    FILTER("Events", 
        AND(
               [Venue] = [_THISROW].[Venue],
               OR(
                  AND([EventStartDate] >= [_THISROW].[EventStartDate],
                      [EventStartDate] <= [_THISROW].[EventEndDate]
                  ),
                  ...
               )
        )
    )
) ...

I hope this helps!

[halmairobb]

Nope, that's not it, but way more better. This part is perfect:

---

@WillowMobileSys wrote:

It seems, since you are validating that the chosen Venue AND Event Dates, that you can simply use your FILTER() expression for both. 

---

But this works not:

---

@WillowMobileSys wrote:

[Venue] = [_THISROW].[Venue],

---

When [Venue] and[_THISROW].[Venue] are the same or there are no match, it works fine. But when I choose more than one value in the form from the list (Venue is EnumList), it runs false. It looks to me, this expression doesn't compare them as two lists. 

---

[KON_TROLL]

Yes, so instead of [Venue] = [_THISROW].[Venue],  you must either use:

CONTAINS([Venue], [_THISROW].[Venue])

or

IN([_THISROW].[Venue], SPLIT([Venue], ","))

[halmairobb]

I think I know what the problem is. This expression works perfect for checking the Venues:

INTERSECT(
[Venue],
SPLIT(EVENTS[Venue], ",")
)

It seems to me, that in the FILTER expression, the Venue check and the Date range check runs separetely. Ie. first the expression check the Venue in the whole table, then the Date range in the whole table.

So if I can filter first the Date range, then in this filtered list i can check the Venues  - it must be good...

TO BE CONTINUED... 😉