Called explicit measures, defining your own. Or maybe you're still dragging columns over to the measure in the visuals. Now you have to understand that that simply creates an implicit measure, Power BI. Is still creating a measure under the hood for you. Either.
Way, DAX measures are what's always powering your visualizations. Now the good news is that non techies like you and me can understand and write DAX and DAX really doesn't need to be the scary thing that techies have made it to be. And I know because even though..
I'm now a Microsoft MVP, a Microsoft Partner Best selling Power BI author, and I've trained and coached thousands of users across the world on Power BI. But I started off as a non techie business user, just like you. And I'm still one at heart. But the challenge was I had to figure out everything the hard.
Way. But that's why I've made it my mission to make it easier for you. So today I want to show you how you can write any DAX measure in just three easy steps. So we going to show you a DAX problem and then we going to walk you through the solution, showing.
Teaching you to fish. So once you know the steps to follow to write any DAX measure, well, now you can catch all the fish you want. Or my friends. So let's start with that. Writing any DAX measure in three easy steps. But one thing I want you to think about before we before we start with the steps is think about.
How valuable would this be to you if you had the confidence to step into any DAX problem? And solve it. What would that enable for you? What would then unlock for you? What's the potential for you to there? What can you achieve? And I would say you can achieve a lot, because.
Again, DAX measures are at the heart of mastering Power BI. So let's get started with the DAX problem. So this question was asked in our live QA call and I'll walk you through. So what the user has done is he they have created this set of tables using filters over here on this side and it's.
Really straightforward at least for us humans and we have categorized that our customers into lost, declining new customers and growth customers and each one of this kind of makes intuitive sense. So lost customers, you can see they had sales in the prior.
Year. They do not have sales in the current year. So therefore there are lost customers and we can add up the impact on the sales. The same thing with declining, they had some sales prior year and while their sales just declined this year and we can calculate the net impact for that..
- Color Gradient Photo Overlays Bundle Download For Photoshop Sheri Sk Photoshop Tutorial
- How to build Professional Excel Add-in. Excel Add-in Development course Office
- How To Create A Personal Financial Planner In Excel FREE Download + Masterclass Office
And of course we do the same for new customers. No sales last - ID Card Make
Year, sales this year, what's the impact, growth customers, some sales last year and what's more sales this year an what's the net change? Year over year. Now of course they were able to do this in.Tables and filters, but what they really wanted to do was to be able to write measures to calculate that. And again, measures do make you more powerful because what they let you do is they let you display a table like this and they let you create like a waterfall chart like this where you can see that.
You know we have. If you notice over here you can hover and say Yu you know, so loss, the impact for loss declining and then new and growth and of course this is the total retty elegant and of course you can slice and dice it. I don't have it here, but if.
You have like a product Tye or product category you could click on that and the whole thing will filter just to that. O again measures give you a lot of power and that's our goal. Now the one we're going to focus on is this growth measure because really if you can write one, the other ones are pretty similar..
So think about how you would write that DAX measure which checks the the product category, checks the customer and sees if the sales grew year over year. Well, so that was that is certainly an interesting problem right now. It doesn't matter maybe you are at the beginning of this journey or you're.
Further on and maybe you know how to solve this problem. Like you say I'm like Yep, I got it obvious, I think I can figure this out or again you're getting started and you're like. I don't really know. That's not the important part. We're just using that. This is an example. Either way, keep watching.
Because again, we're trying to teach you the steps. We're going to teach you how to catch that fish. We're going to teach you how to write any DAX measure in three steps. Let's start with step one. Step one is what I call human learning. And this is very important because before we can teach the machine..
RBI, you first need to understand how we humans are solving the problem. Now it seems like. What are you talking about, Avi? And here's the challenge, my friends. So as of now, humans are smarter than machines, right? And especially because we can make these intuitive leaps and that's what.
We do when we are looking at a problem, we can intuitively jump to the answer. But machines can't quite do that very well, so. So step one is to actually slow down that human learning and instead of that leap you have to break it down step by step and.
Try to understand, almost dumb yourself down. Slow yourself
Down and understand step by step how you as a human are solving that problem before we can go any further. Now this may seem trivial, but. It is not. And again, you're going to have to do some work.Here. So in this case, we were trying to figure out how to get this number. Now believe it or not, this took us 30 minutes to pin down. So again, this was asked in the live Q&A call and I have the recording. It's part of our Power BI Advanced program where I show this problem in the solution step by.
Step. And the original recording is 30 minutes and. All I did was just wanted to make sure that I understand the human solution, the human learning. Now of course, I know your time is valuable, so I'm just going to cut to the chase here. And this is the final steps that we got to right now..
It's going to seem straightforward, but again, hindsight is 2020, right? So when you're working on the problem, make sure to slow yourself down and write things out step by step, just like we did. So the steps here are we going to examine each product.
Subcategory, right? So that's where we start. So we say for each product subcategory and for each one of those, we check each customer, right? Fair enough. And then for that combination, we check whether the sales year of year percentage is greater than 0. Essentially we are checking for growth customers..
If that is true, then we filter and compile that set of customers and oh, these are all. Growth customers and once we have that, we calculate the sales year over year change for this set of filtered set of customers. And in the end we sum it all up. So that's how a human.
Would do it step by step. So that's step one. That's the human learning. Let's move on to Step 2, Step 2. You do what a baby would do and think about how babies learn a language, and that's the same thing for us. DAX is like a new language for us. So I want you to think about how babies start.
To speak a new language. And what they do is that they ignore the grammar or syntax and they focus on just simply communicating what they want. So a child might say, oh, me like doggie, right. All right. So we're going to do something similar. So we're going to focus.
On what we're trying to do instead of kind of the specific grammar and syntax. We're not going to worry about that yet. We'll come back and do that later. So the first step here for human was for each product subcategory, whenever you find yourself writing down or thinking in terms of like that..
For each, think row by row, right? Each you're going subcategory by subcategory. You're going row by row, and that is iterator, and it's represented in Power BI with the letter XY. You'll see that soon we'll meet the X family of functions, right? So X is the iterator. It says row by row. So.
You know, kind of the baby step. Again, it's not proper syntax. I understand that Power BI is not going to understand the X function. But I'm just going to write. I'm iterating X, that's the iterator over the product subcategory. And of course the next one is.
Easy. Next I'm iterating on the customer table, so I'm going to write X customer, right? And after that it's check sales year over year and filter. So for this we're going to say, Yep, check sales year over year percentage greater than 0 and then filter the resulting rows..
The next step pretty similar. Calculate the sales year over year, measure and sum it all up. So again, not that different from human speech, but again, you need this stepping stone to be able to take the step after that. Now of course, if you were to plug this into Power BI..
It really wouldn't work. You would definitely get an error because that because we're not there yet, We're in Step 2. Remember it was 3 steps, so we have one more step remaining. So let's rewrite this for Power BI and that is our step three. And this one is called machine learning. But really this one is.
More like machine. Teaching like we're teaching Power Bi is like, OK, hey, I know how to do it. Let me teach you. I've broken it down step by step. I made it really easy for you. Power Bi let me teach it to you. Right. So. And this time we have to get the correct syntax.
Because again, I mean humans, we are smart. We can deal with a lot of ambiguity. Power Bi cannot. It needs precise instructions. So we're going to give that to Power Bi. So let's get over to our baby steps. And let's translate. But before we do that, though, we got to.
Talk about the Power BI core concepts. Now, these core concepts follow the classic 8020 rule. That means if you put your effort on learning these core concepts, you're going to be putting in 20% of the effort and getting 80% off the results. Which is amazing, because if you don't do that, then you probably.
Know that life you're kind of struggling with the jigsaw puzzle of Google and YouTube. And it can be. Really frustrating because you haven't mastered the core concepts. You're trying to fit this puzzle together. So let's start with the first one, which you actually already know. We.
Have talked about it. I talked about X functions are iterators, they go row by row. So right for each subcategory, for each customer, things like that. Now there's a whole family of these, there's some X min, X Max, X average, X context, and many more. But the more important core concept is that..
X functions work backwards. Yes, I know it took me a while to figure that out. I get straightforward once you know it. But it's important to know this concept. So X functions work backwards. What do I mean by that? That means if you look at some X, minx, Maxx, average X, the X, the iteration row by.
Row happens 1st and then the operation. Happens in the end. So if you just were to pick up one of these, the sum X, and they all work the same way, this is the syntax, right? So it says a sum X in the table and expression. But the way I want you to read this is X, right? So X iterate.
Over the table, that's step one that's over here. And then for each. So iterate is just going row by row, that's what it is, right? And for each row evaluate this expression, evaluate this expression. But the sum happens at the very end, even though it's written at.
The beginning. I wish you could write this this way. It'll make more sense to me. But I want you to remember the score concept and understand. So every time you're reading an X function, I want you to read it this way. All right. And of course, as I said, the..
Each of the other ones work the same way. The iteration happens first row by row, and the first part, some min, Max, average happens at the very end. Now that we know the score concepts, now we can look at our DAX baby step and kind of see that oh iteration happens 1st and the sum happens in the end. Bada.
Bing bada boom. Of course that is a sum X function. But again, so the sum moves first. You write it first, even though it happens in the very end, and then you write the X function. Now this is good. We're making progress here going from human learning to machine learning. But of course, Power.
BI is kind of dumb. No, I'm just kidding. I didn't say that. I didn't say that. But Power BI is very precise. So it's going to say, oops, cannot compute, cannot compute, right. And that's because. RBI, The syntax for SUM X is that it expects a table. Oops,.
We don't have a table. We have a column. We're saying, Oh yeah, iterate over the product subcategory. Hey, but there's an easy solution here using the VALUES function. And that's exactly what it does. It takes a column of values and turns them into a table. So problem solved. So we're going to just.
Substitute that in our function. And we're good with this step. So we have kind of translated the human baby speak to Power BI speak. So I'm going to keep dimming out the ones that we have already tackled just so we know our progress, we can track our progress. So we've already.
Done that one. We have translated that to Power Bi, the machine learning. Let's focus on this one, calculate and we're going to move it all the way up. Why are we doing that? I mean, Calculate is supposed to happen. In the end, we're moving it all the way up. Hold on to that.
