Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
j_hoyt
Frequent Visitor

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

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. 

1 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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

3 REPLIES 3

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/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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. 

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.