Contenidos
I have learned a lot during my 50-something years of life, and one of my greatest learnings is that "No matter how much you already know, there is still more to learn". Hopefully this is one of the reasons you read my blog, because you want to learn from me, we all learn from each other.
My regular readers would know that there are many reasons why I like to share what I have learned on my blog.
- I can share what I know with others.
- I can clarify my thinking on the subject (I get to a deeper level of understanding when I have to explain it to someone else). Stephen Covey describes this as "third person learning"
- I get a permanent record of how to do it myself, so I can find it again in the future when I need it.
- And hopefully, at some point, someone will hire me to train their staff or fix a problem they have.
So sometimes (like this time) I find that someone has a better way to solve the same problem that I shared on my blog. This is what happened last week after I shared my first article on how to calculate total business hours between 2 date / time stamps. I shared how I solved this problem last week, but one of my readers, Daniil Bogomazov, shared a brilliant workaround to the same problem. The solution is so good that today I share its solution with you.
But what about last week's solution?
The solution I provided last week is a valid solution (there is more than one way to skin a cat). Additionally, last week's blog was also about the process of solving a problem rather than the specific resulting formula itself. I strongly believe that if I can help you understand the process, you will be a better DAX author. Note: I got a couple of comments last week that I made a logical mistake in my formula; I must fix it :-).
DAX is a beautiful language
The solution that I am going to share below is literally beautiful. DAX is (or should I say, can be) a beautiful and elegant language. Once you understand the underlying principles of how the language works, you can literally write fancy formulas that take advantage of the strengths of the data model to return the number you need. Let me show you the formula provided by Daniil and then spend some time explaining how it works.
Beautiful and compact, don't you think?
Why is it so different?
You might be wondering why this solution is so different from my solution from last week. I've been thinking about that and I think it's because Daniil approached the problem in a different way than I did. You may recall in the video from last week that I used the following illustration to think about the problem.
I broke the problem into pieces and built a solution for each piece of the puzzle (first day / partial day + last day / partial day + full days in between). It occurs to me that Daniil thought of the problem differently. I was thinking, "I need to get through each work day, one at a time, and calculate how many hours each day." It is a different way of thinking and, more importantly, plays directly with the strengths of the DAX language. DAX is very strong in 2 areas: filtering tables and iterating through rows in tables. So Daniil's thought process plays directly into DAX's strengths - there's a lesson in that for all of us. If you can think like the engine, you will write better DAX.
Differences in Daniil's model versus mine
Before I start to explain this solution, I should point out that Daniil did not split the open and final columns of Tickets into separate date and time columns. Instead it kept them as merged date / time columns.
This is necessary for this solution to work because the date AND time are part of the solution. Generally speaking, it is not a good practice to load data in date / time columns and it is generally recommended to divide it into component parts. It would definitely be possible to split the columns (best practice) and then rebuild the date / time (as required by this solution) within the DAX measure. In production, if size and performance were an issue, I would definitely consider doing it. I've shown you how at the bottom of this page.
The second thing to keep in mind is that there are no relationships in this model.
That makes sense when you think about it, because the primary key column of the calendar table is of type "date" and the HelpDeskTickets table is Date / Time, therefore they cannot be joined (not sensibly, at all modes). The last difference is that Daniil has hard-coded the start and end date / time for business hours in each row of the calendar table. This has the added benefit of effectively supporting the possibility of different business hours on different days, if required. In my alternate formula at the bottom of the page, you don't need to store them as date / time columns (best practice again).
Let me break down the formula for you below
As is often the case in the DAX language, you have to start in the middle of a formula to find out what it does. In fact, it often happens that DAX formulas are written inside out. This is especially true for this type of solution that uses tables, iterative functions, and calculated columns.
Let me start with lines 6-10 (shown again below)
This section creates a copy of the calendar table, keeping only business days (lines 6, 7, and 9 do this). Line 8 passes an additional filter to the Calendar table for each ticket in the HelpTickets table, keeping only the rows in the Calendar table that span the days the ticket was open. Lines 6 through 9 complete this task, one ticket at a time (let's get back to that shortly). Now I said "copy" of the calendar table above, but you can "think" of this formula working in one of 2 different ways. Think of a virtual copy of the table that includes the lineage up to the original calendar table, or you can think of the original calendar table in the model that is filtered directly. Both approaches to thinking give the same effect: the calendar table only contains the days you are interested in at the time you use it, which turns out to be a list of business days spanning the period in which the ticket is open. Because this formula is a calculated column, it is the iteration of the calculated column that passes each HelpTicket to this part of the formula, one row at a time. So you can "imagine" that each row in the calculated column has its own "subset" of the calendar table filtered specifically for use in the next part of the formula.
Line 5 is a SUMX on this table
So line 5 (and the corresponding closing bracket on line 12) is the iterative function. SUMX loops through the filtered calendar table created earlier and then completes the calculation shown on line 11 for each row in the filtered calendar table. It is this SUMX iterator that loops through the calendar table filtered by subsets, one row at a time, to calculate the total business hours for each ticket.
So let's look at line 11 in detail.
This is a pretty complicated line of code, but if you break it down, the logic is pretty simple.
Starting with part 2 (above), it says “which date / time is greater; Is it the start time in the calendar table (for this row I'm looking at) or is it the start time in the ticket table? If I mean my illustration of the weekdays in a week, it says “whichever comes first, 1 or 2 (in the image below). In other words, was the ticket opened before or after the start of business hours on this day? Keep the last one using the MAX function.
Going back to the original formula (shown below again for convenience) ...
Looking at part 1 of the formula above, the question is “what comes first; the closing of the ticket (3 in the previous timeline) or the end of the business day (4 displayed in the timeline) ”?
Now line 11 is quite smart, because if it is the first day or the last day of the ticket, it correctly handles the “partial day” if necessary. For all other days, it correctly calculates the full business hours for that day.
Finally my modified version
As I mentioned before, maybe It is beneficial to split the date / time columns into larger models (it would not have any material impact on such a small model, but it may benefit larger models). I've included a modified version that does this for completeness, and you can see that you can simply rebuild the date / time stamps within the formula as needed.
This is a general technique that you can use on any date / time column. You should always factor in the extra effort from the engine to put the columns back together and also the slightly more complex DAX versus the benefit of saving storage space.
I have attached the workbook here if you want to take a look at it.
