Line Graph roll up by day

[MrMike]

My log table has activity every second and I want to roll up the data by type per day and show on a line graph. For example the data looks like this:

1/25/2021 8:34:57 PM Login User1
1/25/2021 8:37:09 PM Request

1/25/2021 8:39:59 PM Logout User1
1/26/2021 5:34:45 PM Login User2
1/26/2021 6:20:32 PM Login User 1

1/26/2021 6:28:39 PM Request

1/26/2021 6:30:09 PM Logout User 1

I want my line graph to show along the bottom (X axis) the days (not days and time, just days) 1/25/2021, 1/25/2021

also the count of users login and logout per day and the request count per day.

So the line graph would have a point on one for count of users login on 1/25/2021

a point on one for users logout on 1/25/2021

a point on one for request on 1/25/2021

a point on 2 for login on 1/26/2021

a point on 1 for request on 1/26/2021

a point on 1 for logout on 1/26/2021

and because this is a line graph, a line from point to point by activity type to each day.

Currently my line graph shows the time, I don't want time I want rolled up to day, also my counts are all off.

I saw a post about Filter, but I don't know if that is the answer, I tried but the result is way off, I selected a date range and expect to see 3 and it came back with 121. I have no idea how it is getting that number.

Login = CALCULATE(COUNT(ACTIVITY_LOG[STUDENT_ID]), ACTIVITY_LOG[ACTIVITY_DESCRIPTION] = "Login",

FILTER ( ALL (ACTIVITY_LOG[ACTIVITY_DATETIME]), 'ACTIVITY_LOG'[ACTIVITY_DATETIME] <= MAX(ACTIVITY_LOG[ACTIVITY_DATETIME])

)

)

[v-yuaj-msft · Accepted solution]

Hi @MrMike ,

Here is my test table. I add a row as you said.

Then I tried the same steps.

Result:

Can you share some screenshots around the issue please? This is not helping much.

Add error screenshot as well which you are facing.

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MrMike]

It is getting closer to being right.  How did you get your line graph to show one day and not multiple times for a day. Here is my line graph, I want it axis to be by day not by hours.

[MrMike]

I made some corrections and it works now. Thank you v-yuaj-msft. I will accept your answer as solution.

[v-yuaj-msft]

Hi @MrMike ,

Based on your description, you can do some steps as follows.

1. Duplicate a "Action" column and split it by delimeter of "Space".

2. create a date column 

Date = FORMAT([Date/Time].[Date],"Short Date")

3. create a measure

Measure = COUNTROWS('Sheet1')

Result:

Additionally, I also create a calculated table if it can help you understand better.

Table =
SUMMARIZE (
'Sheet1',
[Date/Time].[Date],
Sheet1[_ACTION],
"count",
CALCULATE (
DISTINCTCOUNT ( Sheet1[USER] ),
ALLEXCEPT ( Sheet1, Sheet1[_ACTION], Sheet1[Date] )
)
)

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MrMike]

I did as you showed but the count is wrong. If I add another "Login User1" with date 1/26/2021 then I expect to see 2 logins becuase the distinct count should count "Login User1" and "Login User2" as 2, no matter how many times "Login User1" logins in a day. 

Again I tried both your methods and get the same result.

PerDay =

SUMMARIZE (

'ACTIVITY_LOG',

ACTIVITY_LOG[ACTIVITY_DATETIME],

ACTIVITY_LOG[ACTIVITY_DESCRIPTION],

"count",

CALCULATE (

DISTINCTCOUNT ( ACTIVITY_LOG[STUDENT_ID] ),

ALLEXCEPT ( ACTIVITY_LOG, ACTIVITY_LOG[ACTIVITY_DESCRIPTION], ACTIVITY_LOG[ACTIVITY_DATETIME] )

)

)

[HotChilli]

With a more complex model, I would always say 'use a separate date table' but here it's more straightforward as presented.

To make a date column : In Power Query, duplicate the datetime column and then change the type of the new column to Date.

Close and Apply.

 In a line chart visual, put the new date column on the Axis, Put the Event in the Legend and put Event in the Values section (make it a Count). In the format section, make sure the x-axis is categorical.

I'm assuming the Event column is separate from the User column.

[MrMike]

Looks better but the Event is actually a DAX expression and it is showing a number 1 for each event. For example "Login" event type on 1/26/2021 has two users with login event. The line graph shows a 1 between 5:00 AM and 6:00 AM and then there is another 1 between 6:00 AM and 7:00 AM.

How do I get a 2 over 1/26/2021, so it is summing the whole day?

Login = CALCULATE(COUNT(ACTIVITY_LOG[USER_ID]), ACTIVITY_LOG[ACTIVITY_DESCRIPTION] = "Login")