When I first started working with Power BI, I thought I had to learn all the DAX functions to master Power BI. I was so so wrong. Turns out you just need a handful of functions to do the heavy lifting for you. In this video, I'm going to talk you through the MVFs of Power BI and the DAX language. By MVFs, I mean most valuable functions that you're going to use over and over and over again.
Are you ready to simplify your DAX life? Well, let's start. Now, I won't literally talk about 5 or 7 or 10 DAX functions.
Rather, I would divide all the functions that I have got into 5 broad category of functions. And I'll also talk about the broad category and what kind of problems these functions help you solve. Once you understand the category, you can actually expand the category by adding more functions in that. Alright, the first type of functions that I would like to speak about or the first category is nothing but columnar aggregations. What do I mean by that?
you simply mention a column and it aggregates that column. Take a look at the data that I'm working with. So here I have a simple sales table and perhaps I would like to perform a few aggregations. Aggregations like what are the total number of units? So what is the sum of all the order quantities that are made right here?
Or perhaps the other type of aggregations could be, hey, could you maybe tell me that how many unique products are there? So I would like to count out the uniques in this particular column. These are very simple calculations and what you would put inside of these kind of functions is nothing but just one column.
Take a look at how that works. So let's just say that the first function that I would like to create is nothing but total units. And to be able to create total units, I'm going to write a simple measure, something like this.
So I'm going to say that, hey, I'm looking for a sum calculation. And you can see that as soon as I write the sum function, because it's a column aggregator, it is only going to ask you, please give me the name of the column that you'd like to sum. You can't feed a table, you can't feed anything else, you have to feed in a column of a physical table. So if I were to just go ahead and say that, hey, please sum the order quantity column, close the bracket, press enter.
And if I drag that particular calculation off to my visual, what I'm going to get is nothing but total units. And similarly, there are a few other type of calculations as well, such as a distinct count or a count and things like that. These are all columnar aggregation kind of functions.
So the other one could be unique products. And to do that, I can use a simple distinct count function, which is where it says, hey, again, give me a column through which you would like to find the distinct values, for which I'm going to mention my product. key right here from the sales table close the bracket press enter and now if i drag that into my visual this is what i end up seeing that means in q3 we had a total of 25 products being sold so on and so forth for the rest of the quarters now let's just say that you're trying to find total sales value and if you take a look at my sales table my total sales value is going to be found something like this i have to take the order quantity and i have to multiply that with the unit price now Now, if you were to create a column something like this, if you right click on the sales table and make a new column, so I'll say that I want to make a new column. Sure enough, in the column calculation, I can go ahead and multiply the two columns, which is my order quantity.
So right here and multiply that with the unit price, which is right here, press enter and I do get a column for that. But somehow this particular column calculation that we have made is not going to work in a measure. So if I just go ahead and say control C of that.
and I happen to remove this particular column so I'll just delete it and I'm going to create a new measure let's say I'm going to name my measure called total sales and if I just happen to paste that code right here this is not going to work the problem is that we would not get the access to the row of the table just by creating a measure so we need to move to the second category of functions which are called iterator functions Now, what are iterator functions? Whenever you have the job where you have to step inside every single row of the table and do row by row by row calculations, that's when you need the iterator functions. Funny enough, well, in Power BI or in Excel, while creating a column, this would have been a very intuitive way of creating columns because we have been kind of veteran users of Excel and we have created a plenty of columns in Excel. So what you do is you make a column and by default, you get access to every single row.
But the problem is when you create a measure, you don't get access to the row. To be able to solve that problem, what you need is a second grade of functions or second class of functions, which are called the iterator functions. Simple enough, it means that give me a table and then I can do something for every single row of the table. That's what an iterator function does. So coming back to our original calculation, total sales, I've been trying to write this, but obviously I can't do order quantity into unit price.
Because I have to also mention an iterator function around that to get the access to the row. So I'm going to go something like this. Any function that ends with the word x is an identifier that that function is an iterator function. It will have the ability to do row by row calculation. And most of the iterator functions will definitely have two parts.
It will have the first part as, hey, what's your table? So what's your table that you're trying to work with? And then in that table, I can go. row by row and I can do something. So in my measure I can say that my table is nothing but my sales table and in that table please pick up every single order quantity for every single row multiply that with the unit price and if you happen to close the bracket right here this is going to perform the right result.
Now if I just go ahead and drag that total sales off to my visual sure enough I do get the right calculation and there are several kind of functions which fall under this category of iterator functions. You can have some x min x max x average x rank x all of these functions have these two parts in common which is nothing but your table and what is the calculation that you'd like to do row by row whenever you need the ability to go row by row that's the category of functions that you should be eyeing for in case you're enjoying the video thus far you're absolutely going to love my new book on power query power query beyond the user interface. In the book, I talk about advanced techniques that go far beyond the user interface and teach you what lies behind those clicks of Power Query, especially using the M language to solve more harder and nastier data cleaning problems. The initial few chapters are based on the fundamentals, understanding those basic building blocks like lists, records, working with tables, manipulation of the data.
And more advanced chapters cover like iterations, custom functions, nested tables, and a whole lot. of more complicated aspects that take you to the next level. The interesting thing about the book is that the book has been written in such a way that it contains no jargons. I've used a very, very friendly language to explain the most complicated concepts in a fun way.
And the book contains a lot of diagrams and images to help you cruise through the book as if you were just watching my video, but on print. If you buy the book before the 7th of February, I'm also very happy to give you access to special bonuses which is nothing but some advanced functions in Power Query, custom functions in Power Query and advanced case studies as well. Note that these bonuses are worth $150 and you're going to get them absolutely free along with the book in case you order or pre-order the book before the 7th of February.
I've poured in a lot of care and attention and love into this book and I hope that you thoroughly enjoy the book. Grab a copy today. The problem that you're often going to face with iterator functions is that most iterator functions by default they work on physical tables. So that means that whatever table that you have written right here is going to be physically present in your Power BI in the data model. But what if you have to do any kind of summarization or aggregation of the data and then come up with an answer?
To give you an example let's just say that I'm trying to find out the sales of the max selling country. whatever countries that we have had so let me just actually go ahead and show you the sales table so whatever countries that we have it right here i would like to be able to find out that of all of the countries which sold in a certain quarter which of them had the maximum sales and how would you actually do that so actually i'm going to have to do something like this so i'll have to go ahead to my visual and try to visualize the answer so let's just say that in 2001 in q3 we had three countries so country one country two and country three and then i have to find out the sales for every single country Once I have found out the sales for every single country, I then have to pick up the maximum sales and then list down that sales right here. That is what I would want to do.
The problem is that at the moment, if I actually take a look at my sales table, my sales table is at the granularity of the transaction. That means on one single day, I could have had multiple transactions. It is not at the granularity of the country.
It is actually at the granularity of the transaction. So the next category of functions which are going to be super super helpful are going to be your table summarization functions. Let me show you one example of that.
So we're going to go ahead and we're going to write a measure. The measure could be max country sale. And in there I would first want to make a summarized table which is where we can use functions like values function, summarize function and summarize columns function, things like that. But for now I will just use the values function.
And I'm going to say something like hey why don't you take the countries column from my sales table. and I'm going to say, actually, this is not value, but values. So, hey, why don't you take the sales table and from the sales table, please take the country column and from that country column, why don't you remove the duplicates? Now, this particular formula is giving me the list of all the unique countries. Now, then I'm going to say that, hey, I would want to go ahead and step inside every single row of these unique countries.
So, country number one, country number two, and country number three. And then against that, I want to find total sales and then pick up the maximum sale. right so how do we do that i'm going to go ahead and say something like please go ahead and in every single row calculate my total sales close the bracket and press enter now this is going to make the unique list of the countries against every single country i want to find the sales value and then i want to pick up the max value against that now if you actually happen to click on ok and press enter if you drag this formula off to our pivot table you're going to see that what we get is that the total sales of the entire countries all The countries put together was about this much, but only one single country, which was the maximum selling country, brought this much sale.
Now, whenever you do calculations like these, whenever you're trying to summarize the data, you'll end up seeing that for non-editive calculations like finding out the max, the totals are not going to match up. Take a look at this. So our max selling country in Q3 was about 600,000. This is 700,000 and that produces about 1.3 million. But if you actually take a look at these four numbers, these four numbers definitely.
do not add up to 2.1 million. So what do we do about these calculations? How do we make sure that the totals are actually fitting in right?
Now, these are semi additive calculations. Now, what you need to do is you need another segment of functions, which is within the summarization functions, you need enhancer calculations or enhancer functions. What do I mean by that?
Please take a look. So let's just say that I would want to find out the max country sales for every single quarter, and I've gotten the answer, then I want to add all of these max values at the total and the total should not be the max but the total should be the sum of all the maxes well we can do that using some add column functions which is going to enhance the table or the summarize table that we have made please take a look so i'm going to go ahead and create this measure called max country sales version number two and i'm gonna say that he First of all, I'm trying to create this particular table, my year and the quarter table. That's what I'm trying to create. So I'm going to go ahead and say that I'm trying to summarize, which is also one of the summarization functions. But I will enhance this calculation with another measure.
So please take a look. So I'm going to say that, hey, I'm trying to summarize my calendar table. And the summarization factor are two dimensions, which is nothing but my year. And the second one is nothing but my quarter.
Once I do that, I actually close the bracket. And this is. nothing but this two columnar table that I have created. Now in this two columnar table what I want to do is I want to find out what these values are. Now what I can do is I can use one of the enhancer table functions that is going to add a new column to this table. So I'm going to say that a function or a category of function that I'm trying to use is nothing but an add columns function.
Now this add column function what this is going to do is this is going to take our existing table which is just two columns which is the output of the summarize function and add this additional calculation right here. I'm going to say that the new column that I'm trying to create is nothing but M, M as in max. And then the actual calculation is nothing but the max country sale that I just created a while ago.
And if I just happen to close the bracket, now this particular measure is actually giving us a three column nut table, which is where column one is this, column two is this and column number three is this. And now that we have made this enhanced calculation and a table calculation, we can go ahead and step inside this table and perform like an iterator and sum every value up, which is there in the M column. And now I can go ahead and start to write something like a sumX function around this. And I can say, please go ahead and perform a sum of every single row and which is going to belong to the column M. And that's pretty much it.
If I close the bracket, press enter. And this. calculation if I happen to drag it into my visual you're going to see that you actually get the right total. So here instead of getting 2.1 we are getting 2.3 million as the correct total.
So you can use these enhancer calculations like add columns functions to enhance the tables that you have made and this is actually going to solve the totals problem in quite a lot of ways. In case you do not know how to solve the totals problem I've done an extensive video on fixing the totals and I suggest that you do watch that as well. Quick introduction in the video in case you're liking the video so far and you're wondering that how can I learn these skills of writing good DAX, good data modeling, the M language behind Power BI and all of the nuances of Power BI that makes it work.
I have brilliant courses on Power BI especially the Power BI query part, the DAX part, data modeling part and the M language part. In the courses I take you from a beginner level and take you to more advanced concepts, try to explain you the logic of how things are working. so that you feel confident in applying the learnings to even on your own data sets.
Hundreds and hundreds of students have joined my courses and they have benefited a lot. In case you're interested, the link for the courses is going to be down in the description of the video. Let's get back to the video. The fourth category of functions are super helpful and you are going to find yourself using them in a lot of scenarios. And these I call it as exclusion or inclusion kind of functions.
What do they do? They're going to be able to check that is. the two tables having the similar number of rows or not or do you have any extra rows added or are there any new items or are there any matching items between two tables or not and i will present you one fine example of that so let's just take a look at our sales table and in the sales table we have a customer key this actually denotes that what customer did buy your product now if this would have been a sas company one of the very very important metrics for us to calculate would have been please find out the new customers that I have acquired every single month and let me show you how can we do that. The idea of finding out the new customer is something like this. You take a look at all the customers in the current month, you take a look at all the customers in the previous month and then you find are there any new customers added as compared to the previous month.
Any new customers which are added, the count of that is nothing but your new customers and that is exactly what we are going to do. So how does this exclusion function work? I'll show you one brief example.
So let's just say that I'm trying to create this measure called new customers. for which I'm going to form two tables, one table for the current month, the next table for the next month. So I'm going to call this as current month.
Let's just create a variable. So var current month. And I'm going to say that, hey, my current month is going to be nothing but the list of all the unique customers. And we've seen that we can summarize using the values function, the list of all the customers.
So I'm going to say that, hey, I have the customer key in the sales table and that is going to give me the list of all the customers. It's a list of all the customers. It's not a number yet.
Now, I'm going to declare another variable and I'm going to call this as my previous month. So, previous month. And I'm going to say that I still want to do this particular values calculation, but this values calculation should be done in the context of the previous month.
So, I can use the calculate table function around that. I can say that I want to make a table calculation and the table is right here. But on this table, I want to put a filter, which is nothing but my previous month filter.
And I will pick up my calendar date from here. Close the bracket and press enter. Now...
What I want to do is, I want to find out that from this previous month table, how many customers are such that were not there in the previous month right here. And the count of that is going to be nothing but my new customers. So to be able to do that, what I'm going to do is, I'm going to use a function called the accept function. Now, if you take a look at the IntelliSense of the accept function, what it says that it is going to return you the rows of the left side table that do not appear on the right side table. That means if I happen to write my current.
month right here and my previous month right here it will tell me which rows of the current month are were not there in the previous month so all that i have to do is just write current month and previous month right here and press enter now at the moment this formula is not going to work because the accept function returns you a table but hey i want to count the number of rows in the table so i can just say hey i want to count the number of rows in this particular table close the bracket press enter and we are good to go so if i just go ahead and drag this formula off to our visual what we're going to get is the list of the new customers. Now, there are a couple of exclusion and inclusion functions in Power BI. Two of such functions are your accept function and your intersect function.
The final class and the category of functions, which is super important in Power BI, are called context modifiers. What do I mean by that? They will simply be able to modify the context of the visual and bring additional context into your calculation.
Well, if all of that sounds like a jargon, please take a look at my screen. So if you take a look at my sales table right here, we have actually got two dates. We have an order date and then we have a ship date. And you're going to find that sometimes what you're going to see that the order is done in a single month, but the shipment is actually done in the next month. So obviously, when you are taking a look at your sales, that is not going to be equal to your shipped sales.
Well, how do you actually alter that context? And how do you actually consider the ship date calculation instead of the order date of the total sales calculation? Well, what you have to do is you'll have to modify the context.
And one such example of context modifier function is nothing but use relationship. Well, there are several others like all, all except and the entire class of the time intelligence functions like Y today, total Y today, dates add and whole bunch of these functions. But for now, let's just take a look at one such function.
So I'm going to go ahead and first create a dual relationship. So I will maybe go ahead and take my ship date, which is somewhere right here. ship date key and I'm going to link that to my calendar date. Now from the calendar table we have two relationships coming so one is going to be the standard relationship which is order date and the second one is going to be the dotted relationship or the inactive relationship which is nothing but the ship date. Now we're going to go ahead and say that hey this year and the quarter coming off from the calendar table should not hit my order date but should actually hit my ship date and we can modify that context.
So we're going to write a measure something like this. I'm going to say that I want to make a new measure. In the new measure, I'm going to say that I want to find out my total shipped sales.
And that is going to be nothing but my total sales calculation. So I'll say calculate. I want to do my total sales calculation.
And then I would like to mention my modifier, context modifier, which is going to be, hey, please don't. come and touch my order date please go ahead and use the relationship which is calendar date and the inactive relationship which is nothing but my ship date close the bracket and press enter and you're going to see that there is a slight difference in the sales as well so if i just go ahead and drag that right here you're going to see that in this particular quarter in q3 we had a total sales of this much but the shipped sales were slightly less because some of them were not really shipped and they were shipped in the following quarter which is the ship date sales is slightly less than