Count of a particular column based on another column of the different table

[Anonymous]

Hi,

I am new to Power BI and will appreciate any help with my question.

I have a table named 'Datetime' with hour data as shown below:

I have another table 'Info_table' with student information as shown below:

I want to add a new column in the 'Datetime' table which will count the arrivals of students in each hour period as shown below:

Here, 'Arrivals count' is 2 for '1/1/2020 21:00' because student ids 234 and 789 arrived between 21:00 - 22:00 hr as can be seen in the 'Info_table'.

Please let me know how this can be achieved.

Thanks!!

[Greg_Deckler · Accepted solution]

@Anonymous Start with no relationship between the tables. Add a calculated column like:

Arrivals count column = 
  VAR __StartTime = 'Datetime'[Datetime]
  VAR __EndTime = __StartTime + 1/24
  VAR __Table = FILTER('Info_table', [Arrival] >= __StartTime && [Arrival] <= __EndTime)
  VAR __Result = COUNTROWS(__Table)
RETURN
  __Result

[Anonymous]

Thank you @Greg_Deckler !! This works perfect.