topic Re: I want to create a table for a report where I can get the count of uses connected by hours in DAX Commands and Tips

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

luisecp — Sat, 13 Nov 2021 00:45:55 GMT

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.

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

Jihwan_Kim — Sat, 13 Nov 2021 13:22:16 GMT

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]

)

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

luisecp — Sat, 13 Nov 2021 14:54:35 GMT

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