Using SUMMARIZE to get a Maximum Per Day in a Period

A quick snippet on the following scenario. I had daily data of users sessions in a system and wanted to know in a given period what the maximum number of unique users on a single day was. My data looked like this:

Data sample
Data sample

A user could connect multiple times per day, so I needed a DISTINCTCOUNT to get unique users. However for a given period I needed to know this per day. So for the period I needed to calculate the number of unique users per day in that period – which meant I needed to create an interim table using SUMMARIZE.

This resulted in this construction:

SUMMARIZE(Logs,[Session Date],”UsersPerDay”,DISTINCTCOUNT([User]))

“Logs” is my source data table. “Session Date” is what I am grouping my table by to get the results per day. The context of the period I am looking at (be it year, month, quarter, whatever) is managed by the date filters I apply to the table. “UsersPerDay” is just the name I assign to my measure, which is the DISTINCTCOUNT of the User field.

What I will end up with is an interim table which has – per day – the number of distinct users. Though it will not be materialised, in memory it would look like this:

SUMMARIZE result
SUMMARIZE result

Then, to get the Maximum in a day for a period, we just need the MAX of the the UsersPerDay in this table. As it’s an expression, we lean on MAXX:

MaxUsersPerDay:=MAXX(SUMMARIZE(Logs,[Session Date],”UsersPerDay”,DISTINCTCOUNT([User])),[UsersPerDay])

And there we have it! Note in MAXX the Expression we use to get the MAXX of is our custom “UsersPerDay” we created in the SUMMARIZE function. Intellisense won’t pick this up as it’s not part of the model but the formula works just fine.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>