I want to create a table for a report where I can get the count of uses connected by hours

[luisecp]

I have a table with users that start and end a connection

Table1

Users          START_TIME_UTC                     END_TIME_UTC

User1    2019-07-12 01:00:31.000      2019-07-12 04:30:00.000

User2    2019-07-12 02:22:00.000      2019-07-12 04:22:29.000

User1    2019-07-12 03:14:28.000      2019-07-12 04:20:00.000

User3    2019-07-12 01:05:00.000      2019-07-12 06:05:24.000

I create a table in DAX with DATETIME increasing un hour until today

Table2

    DATETIME               COUNTUSERSCONNECT

2019-07-12 01:00:00              0

2019-07-12 02:00:00              2

2019-07-12 03:00:00              3

2019-07-12 04:00:00              4

2019-07-12 05:00:00              1

2019-07-12 06:00:00              1

2019-07-12 07:00:00              0

I don't know how calculate or COUNTUSERSCONNECT, it should check datetime with table1 and count how many users are connected in this hour.

I try it, but doesn't work

COUNTUSERSCONNECT =
COUNTAX (
table1,
AND (
table1[START_TIME_UTC] < table2[DateTime],
table1[END_TIME_UTC] > table2[DateTime]
)
)

It count everthing.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood correctly your question.

Please try the below.

It is for creating a new column.

Count_Users_Connect CC =

CALCULATE (

COUNTROWS ( VALUES ( Table1[Users] ) ),

FILTER (

Table1,

Table1[Start_Time_UTC] <= Table2[DateTime]

&& Table1[End_Time_UTC] >= Table2[DateTime]

)

)

[luisecp]

Thank you, I worked, I've just did it in another way:

Count_Users_Connect CC2 =
VAR __createdDate = Table2[DateTime]
RETURN
CALCULATE (
COUNT ( Table1[Users] ),
__createdDate >= Table1[Start_Time_UTC],
__createdDate <= Table1[End_Time_UTC]
)

I am thinking now, how get the users connect in each moment. 🙄