CHEAT SHEET below, I'm going to send you my CHEAT SHEET containing the top five DAX functions and how to use them. I'm Avi Singh, a Power BI Microsoft MVP, and through my online program on learnpowerbi.com, I have helped thousands of business users master Power BI and build.
Beautiful dashboards and reports in under 30 days. So let's show you the breadcrumb approach to debugging any DAX. So this was a DAX measure sent to be by one of my followers on LinkedIn. By the way, if you're ever stuck in Power BI, you need any kind.
Of help or have any questions, just send me a message on LinkedIn and I will try to help you the best I can. So they explain this to me and the idea is pretty simple. So they have some kind of a support ticket database over here and they're trying to calculate the SLA or the.
Meeting, the SLA time to acknowledge things like that. And for that, they needed something really basic and straightforward. They wanted to calculate the next business day. That sounds simple and straightforward, but this is.
Stay focused on that. And I could see that they were trying to make adjustments for the weekend and holidays and things like that. And they pointed out where this was not working. So you can see that for most days it is working..
So for Wednesday, it returns Thursday. And this is the one that was problematic for Thursday. It was supposed to return Friday, but instead it was skipping over to Monday. Now everything else seemed to work..
Friday returns Monday, the next working day. Same thing for Saturday and Sunday returns the next business day. And yeah, Monday, Tuesday are working fine. So we needed to focus on this one..
So again, it's not about the next business day. But I'm going to show you when I'm looking at any measure, it could be my own. Maybe I wrote it just now or a month ago. And now I'm scratching my head like, how did I do that?.
Or I'm looking at somebody else's measure. So for the breadcrumb approach, it's a pretty low tech approach, but it's really effective. So I'm just going to break it down crumb by crumb. Yep, you got it..
Posts Related:
- Best PowerPoint Infographic Template Free Download
- How To Create A Spa & Salon Manager With Drag & Drop Scheduling AND 1-Click Invoicing In Excel
- Webinar Building Software Product with Make
So I'm going to click here and select new measure.
And I'm just going to paste the same measure here. And I'm going to pick the first crumb, which is the current date. And I'm going to return that here..So I'm going to change the name of the measure to, you know, just whatever the bread crumb is. And I'm going to use the V as a prefix so that so that I can tell you know these later on. And delete them once I'm done debugging and fixing my measure..
So I got the current date and now let's go back and add this to our table over here. And usually when I'm building a breadcrumb, I'll remove that measure. I'll come back to it..
I'm building it step by step and I'm just going to format it. So let's change this to short date. This is pretty straightforward. And then I'm going to build the next bread crumbs. So I'm going to copy the formula again, the whole thing, go back,.
Repeat new measure, paste it. And now I'm going to pick the next crumb here. Next day one put the prefix. We make sure I'm returning that crumb in the return value. And that one is good..
And I'm going to drop that bread crumb in here. And I'm going to format this as a long date, OK? And maybe I'll speed things up. All right, I think I'm going to stop here. For one, I'm kind of suspect of the holiday logic over here..
Plus I did spot the problem. It's actually up here in the adjustment, but you can see how the breadcrumb approach helps us figure that out step by step. So let me put the at least a part of the measure side by side so we can look at it..
All right. So notice the first crumb is the current date. And that one is super simple selected value of this date. So yeah, it's it's nothing smart May 22. It just returns that date and that's OK..
That's how measures should be built, Lego block by Lego block, bread crumb by bread crumb. So we current date is pretty straightforward. And the next one we're adding plus one current date plus one. So 522 becomes May 23rd, this becomes May 24th..
You get it.
So far so good. And then we get something interesting next day or week. And this is slightly interesting because we're using the weekday function with this optional parameter..And what that does is it makes sure that the Monday is numbered one and Sunday is 7. I I find that easier to think about as well. So yeah, so Monday, you can see it returns a weekday. Mondays are one, Sundays are 7, Tuesdays 2, and so forth..
So, so far so good. And then we have the adjustment and we can see exactly the adjustment going in. And here you can see the problem. So for Saturday, we want to push it out to Monday..
That's the next business day. So the adjustment is 2. But and for Sunday, the adjustment is one. These two are correct. But of course the, the, the record that we were having.
Problems with was Thursday. And you can see that this is wrong. It's, it's saying I'm on a Friday, but it's pushing it out. And of course, now that I can see it, I can look through the logic and say, hey, there's something wrong here..
It's if it's Friday, I shouldn't be moving. So let's go back and let's put the measure back on. All right? And so again, notice this is Monday. That's not right..
And I'm going to go back to my measure next business day and I'm going to comment out this bit. But now you can see that, hey, Thursdays point to Friday as the next business day. But again, the goal of this exercise was not to guide you on.
How to do next business day. I could speak for at least 10 minutes, if not an hour, on why this is actually not the right approach to calculate the next business day. But this did make an excellent example for showing you how you.
Can take any DAX measure and just break it down crumb by crumb, step by step and debug it. So you don't have to be frustrated, You don't have to pull your hair out. Now it does help to have a good understanding of basic DAX.
Function. And that is why if you comment below the word CHEAT SHEET, I'm going to send you my top five favorite DAX functions that every power we have professional should know and should master. And the CHEAT SHEET is going to tell you what they are and.
