Eventlogg

[MikeSW]

Hi

I have a table where a system logs each time a session ends. When that occurs, I get a log which tells me Who, when it started, and when it ended.

From that I want to show how many that where active for each hour for a period of time. 

And I want to be able to use the table as filter

ID;Name;Start;End
E.g.
"123456BA";"Mikael";"2020-05-11 12:03";"2020-05-11 15:03"
"123456BC";"Ann";"2020-05-11 10:15";"2020-05-11 12:03"

"123456BD";"Tom";"2020-05-11 13:23";"2020-05-11 16:01"
I would like to get a graph that show all logs that was active for each hour.
The graph should have time as the X Line, and the count of logs that has the time between the date.
E.g.

The above would be:
2020-05-11 10:00 0

2020-05-11 11:00 1

2020-05-11 13:00 1

2020-05-11 14:00 2

2020-05-11 15:00 2

2020-05-11 16:00 1

( I know that I miss out on those that are shorter than one hour)

I dont know if all this makes sense.

[v-deddai1-msft · Accepted solution]

Hi @MikeSW ,

Would you please use the following measure:

Measure =

VAR A =

    MAX ( Table2[time] )

RETURN

    CALCULATE (

        COUNTROWS ( 'Table' ),

        FILTER ( ALL ( 'Table' ), 'Table'[Start] <= A && 'Table'[End] >= A )

    ) + 0

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ef8dUQMVWjpOkJ9FR5...

Best Regards,

Dedmon Dai

[MikeSW]

Thanks, that worked very well.

A follow up question, is it possible to link those 2 tables so I can use the logtable as a filter ?

@v-deddai1-msft 

[v-deddai1-msft]

Hi @MikeSW ,

I didn't see two tables in your original post. Would you please verify it?

Best Regards,

Dedmon Dai