Dynamic segments
If you’re reading this blog post despite what is possibly the most boring title in the history of blogging, I salut you! Thanks for giving it a try and I’ll do my best to provide something for you that you will have good use for. OK so this idea of a dynamic segment first when I made a report for a customer where they evaluate feedback during their sales training. This organization is preforming sales training for their clients and instead of sending out a form afterwards asking for feedback, they always have their participants answer a feedback form before they wrap it up. This gives quite a lot of data which is awesome! OK so the issue was that they didn’t just want to know how many unique answers they’ve gotten but they also wanted to know how many training sessions each trainer have conducted.
I had to find a rule. In this case it was actually quite easy because each training session is always a few hours and they are always conducted before or after lunch. I simply concatenated the trainer ID with the date of the training and a 1 if it was conducted before lunch and 2 if if was conducted after lunch.
In other cases it might not be as easy and if you are currently struggling with such an issue, search no further! I made this calculations to look into if it you can spend less time by spending more time when you’re reporting time. As a consultant, I have to report my time. It comes with the territory. But some report their time weekly, others monthly and personally I prefer daily. But one person might just have 1 assignments, others might have 7 at the same time. I need to find a way to group what time entries belong to the same time reporting session. This is what I mean by Dynamic segments!
So how do we do it? Well let’s start with the data we need. In my case I need the group (Employee ID) and the index (CreatedAt).
The first column I want to create will provide a true or false value if the time reporting entry is a new reporting session or if it’s part of a current time reporting session. The code look like this as a whole and can be copied below.
Is new Time reporting session =
VAR CurrentUserID = 'Time Reporting'[EmployeeID]
VAR CurrentCreatedAt = 'Time Reporting'[CreatedAt]
VAR ThresholdMinutes = 30
Return
CurrentCreatedAt -
// Subtract the current CreatedAt with the last time entry before this one
CALCULATE(
Max('Time Reporting'[CreatedAt]),
FILTER('Time Reporting',
'Time Reporting'[CreatedAt] < CurrentCreatedAt &&
'Time Reporting'[EmployeeID] = CurrentUserID
)
)
// If this row was created within the threshold of minutes of the last time reporting entry, this returns true otherwise false
> (ThresholdMinutes/1440)OK so what does it do? It basically goes through the entire table row by row and for each row, it looks at that users last row before this one. If this row is created longer than 30 minutes after the one before, I consider it a new session. This threshold can be changed using a variable in the top.
The column now looks like this! We can see that the first row is true (i.e it’s a new time reporting session) but the second is false because it was made 10 seconds after the one before. OK så we now know that the first 4 rows are the same session, the following 5 belong to the same session etc.
The second column will create the actual ID for each session. I need a unique ID and here’s where the dynamics really come into play. As above, there are different amount of time entries belonging to the same session. To solve this, it’s actually quite simple. We just count the amount of False rows above the one we currently look into! Since we have multiple employee ID’s, I’ve added that number as the decimal so that everyone really gets their unique ID for their sessions.
Consider for example employee ID 1 and 11. Their session 11 and 1 would have the same ID if it was just a whole number, but divided by a decimal makes them unique.
Our new column looks like this:
Time reporting session ID =
VAR CurrentUser = 'Time Reporting'[EmployeeID]
VAR CurrentCreatedAt = 'Time Reporting'[CreatedAt]
Return
VALUE(
CALCULATE(
COUNTROWS('Time Reporting'),
FILTER('Time Reporting',
'Time Reporting'[CreatedAt] <= CurrentCreatedAt &&
'Time Reporting'[EmployeeID] = CurrentUser &&
'Time Reporting'[Is new Time reporting session]
)
)
&"."&CurrentUser
)And the column looks like this! Tadaaa!
Now let’s go over to measures. To get the amount of unique time reporting sessions, we just need to count the distinct values.
To get the time spent on reporting we need a bit more advanced DAX formulas. It looks like this.
_Time reporting session minutes =
SUMX(DISTINCT('Time Reporting'[Time reporting session ID]),
VAR CurrentReportingID = 'Time Reporting'[Time reporting session ID]
VAR MinuteForFirstEntry = 1
Return
(
(MinuteForFirstEntry/1440) +
CALCULATE(
MAX('Time Reporting'[CreatedAt]),
FILTER('Time Reporting',
'Time Reporting'[Time reporting session ID] = CurrentReportingID
)
)
-
CALCULATE(
MIN('Time Reporting'[CreatedAt]),
FILTER('Time Reporting',
'Time Reporting'[Time reporting session ID] = CurrentReportingID
)
)
)
* 1440
)OK so what is going on up there? Well we start by going through a distinct list of each time reporting session ID, we’ll add 1 minute (for the first time reporting entry) and then we add the amount of minutes spent between the first and the last time entry.
And there we go! We’ve created dynamic segments, in this case segments of time reporting sessions. This method could be used in any case where you have a transactional log and want to group rows together. Like a list of drilling in teeth that was done at most 10 minutes in between, a list of reps in your home gym that happened in maximum 1 hour from each other etc.
If you use this method or are benefited from this blog post, please reach out with some feedback or just to tell me what you used it for. Good look out there!
Cheers!
About The Author
