Forum Discussion

MrMike's avatar
MrMike
Helper II
5 years ago
Solved

Line Graph roll up by day

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

     

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    MrMike
    Helper II
    5 years ago

    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] )
    )
    )