How should I model this? A table of scheduled events, and a table of people entering and exiting.

[j_hoyt]

The data model is not revealing itself to me. 

I have a table of schduled events that are 30-45 minutes long. (say, Event ID, Event Start DateTime).

And I have a table of when people enter the area. (say, Person ID, Entry DateTime).

Assume something like, if they enter within 15 minutes before or after the Event Start, I declare them to have attended.

How can I model this to see how many people were at each event? 


I'll say that I already solved this by merging the two tables in Power Query on Event Date and Entry Date, then filtering for the times that met my criteria. But I have a feeling that I should solve this through a proper model. 

[MattAllington]

I would treat this as a banding exercise. The second table, when people enter the area is the "transaction" data but it lacks the "event" that they were assumed to have attended. You can band it using PQ or DAX. In short, add a new column to the transaction table. The code needs to filter the event table so only one row remains, the row that is the event. Then the event code can be added to that transaction in the table. 

the general concept is covered here https://exceleratorbi.com.au/banding-in-dax/

[j_hoyt]

Thank you, that is a helpful pattern to know. It gets me one value from the Event table, but it would be nice to have more (Event Name, Event Start Time, other Event data, etc). I got the Event ID into the Transaction table, but I couldn't make a relationship between Transaction and Event because it would make a circular dependancy. 

[MattAllington · Accepted solution]

You have 2 choices.
1.load a second full copy of your dim table and join to that

2.add the column in PQ instead. Doing this is harder and beyond the scope of what I can type on my iPad.