Comparing multiple columns between multiple tables

[owenv]

I have a table (table 1) with data for assets in specific geographic areas with an 'entry time', 'exit time' and 'region id'.

I have another table (table 2) which contains data for certain incidents in those same areas. This table has a 'date of incident' and the same (related) 'region id'.

I want to add a column to table one that is the result of the following rules:

- region id is the same
- entry time is before date of incident
- exit time is after date of incident

Is anyone able to help me out with this?

EDIT: I'm looking to return a boolean value, or more preferably (not sure if this is possible) a count of all the incidents that happened between the entry time and exit time, providing the region id matches.

[v-janeyg-msft · Accepted solution]

Hi, @owenv 

If you can share some sample data, we can provide more specific code.

According to your description, you can try:

COUNTROWS (
    FILTER (
        Table2,
        table2[region id] = table1[region id]
            && table2[date of incident] >= Table1[entry time]
            && table2[date of incident] <= Table1[exit time]
    )
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,

Community Support Team _ Janey

[amitchandak]

@owenv , Not sure which column you want from table 2

Try a new column like

maxx(filter(Table2, table2[region id] = table2[region id] && table2[date of incident] <= Table1[exit time] && table2[date of incident] <= Table1[exit time]), Table2[Column])

[owenv]

This is close. Apologies, I should have been clearer. I'm looking to return a boolean value

@amitchandak I've just edited my post above but thought I'd add it here. Not sure how these forums work yet and if you get notified of an edit to the original post. I thought after I posted this reply that more preferably (not sure if this is possible) I'd like to return a count of all the incidents that happened between the entry time and exit time, providing the region id matches.