Hello everyone, I hope you are having a great day today. Have you ever wanted to make a measure in Power BI but didn’t know where to start? Well look no further! Throughout today’s blog I am going to be going over some different DAX functions to show why this stuff is dope. Without further ado, let’s get into it.
I am going to start basic and show you how to create a measure, but first, you need to know how to create a measure? Basically, I am going to show you how to get to where you will be writing your DAX expressions. So, while in the Report View (where you put all of your visuals/charts) you have a few options here.
First option: From the “Home” tab if you look all the way over to the right you will see the “New Measure” button. Click that and you will have the Expression Builder appear on your screen. There is also the “Quick Measure” button which I will cover at the end.
Second option: You are going to want to click on the “Modeling” tab at the top of the application and you will see the “New Measure” button.
Third option: You are going to want to click on the name of the table that you want the measure to go into. When you click on the table that you want, the “Table Tools” tab will appear, and you will see the “New Measure” button.
Fourth option: You are going to want to right click over in the data pane (if you want it to go into a specific table then right click that table). After right clicking the in the data pane a box prompting you with different choices will appear, one of which being “New Measure”.
That about covers all the ways that you can create a new measure from the Report View. Moving on, we are going to get into the actual fun part and look at some actual expressions. First on todays docket is the SUM function. This is about as basic as you are going to get. While in the expression builder you should see it say “Measure = ”, whatever you have BEFORE that equal sign becomes the name of the measure. For my example I made my measure “Sum_Sales” and it looks like this.
Just to show what the different expressions are doing, I am going to showcase the data within a table visual alongside one other column. This is what the data looks like.
When you are done, press enter and the measure will be loaded into your table.
Moving up in complexity a bit we are going to look an Iterator function. Iterator functions do their calculations by going row by row through the tables or measures you have as the arguments. The Iterator function that I am going to show you is the SUMX function. All Iterator functions will have an X at the end of their names. Additionally all of them have 2 arguments. 1 is the table it is iterating through and 2 is the expression that the function will use to iterate through the table.
In my example I did the Total Sales from the Orders Table. To do this I had the function Iterate through the order table and the expression that it used was the selling price of the items in the orders and the amount of items that were ordered. The DAX would look something like this.
Not too bad but we still haven’t hit the top yet. The next expression I am going to cover is for if you wanted to get how many transactions there were over the last 30 days. This is a Measure that I created that returns all transactions over the last 30 days. I start by using the Calculate Function and the expression what it will do is Count the Rows from the Order Table. Then I filter that saying that I want the Date that it finds to be less than or equal to the Max of the Order Date and also have the Date greater than the max Order Date minus 30 days). The expression would look like this.
As much as I would love to keep going, there are hundreds of different DAX functions that I can’t possibly cover in one blog. I will have more blogs dedicated to more measures and some dedicated to a single measure. So hey, now there’s something to look forward to since DAX is DOPE! Thanks everyone for reading and I hope you have a great day!
Bailey McDonald
Data Engineer, Patriot Consulting
Email: bkmcdonald@patriotconsultingcorp.com | Blogs: Patriot Consulting Blogs
LinkedIn: Personal: BaileyMcDonald | Company: Patriot Consulting
