Counting activity per day based on type of activity

[Stuquan]

Hi everyone,

I have a dataset that looks like this:

CreationTime	Activity
02/11/2022	CreateReport
02/11/2022	CreateReport
03/11/2022	ViewReport

What I want to be able to do is group up each of the dates by the activity 'CreateReport', so I will see that 02/11/2022 had 2 instances of that activity type and so on for more dates. My overall plan is then to get this into a line graph to show overtime how many reports were created per day and/or month.

I'm a bit stumped where to start, I was thinking some kind of distinct count based on a filter? Could someone point me in the right direction?

Many thanks!

[Stuquan · Accepted solution]

Hi,

I think I was possibly overthinking the solution, here is what worked for me:

CreatedReports =

CALCULATE (

COUNT ( Logs[Activity] ),

FILTER(Logs, Logs[Activity] = "CreateReport"

))

This counts up the activities and simply filters to find the activity I needed. This works and looks good in my area chart.

[Shaurya]

Hi @Stuquan,

You can use this formula to create a new table that filters the table by activity CreateReport and then shows the number of records for every date:

Result = SUMMARIZE(FILTER('Table','Table'[Activity]="CreateReport"),'Table'[CreationTime],"Count",COUNT('Table'[Activity]))

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI