Join Ron Davis for an in-depth discussion in this video Using the DAX coding interfaces, part of Excel Power Pivot: Data Modeling.
- The Dax coding interfaces. When you're creating a calculated column, you're going to need to do it when you're in the data model in the view that we're in right now, just like we did in the last demonstration, but when you're creating the calculated fields, which should be the focus of your Dax formulas, then you're going to create them either here or actually in Excel. There's advantages and disadvantages to both. Here in the data model, if you see, when we click on a Dax formula, we have this calculated field, we have the value shown down in here, so after we ran the calculation, we have the value, and that will help us, and it will show it to us.
We don't have that if we go over to Excel, which I'll show you in a minute. What we do have here and over in Excel is IntelliSense, so if I click down here, and I create a new calculated field. I'll go to the Formula bar, and I'm just going to type in "newname:" The colon, which tells the Dax engine that now I'm getting ready to do something, and then my equals, and now I can enter something like a C, and I get IntelliSense. This opens all of the functions that I have, and I can just double click them and add them in, and you see IntelliSense actually worked here.
Now it wants me to enter an expression, and then I can go through and enter filters. If I would have done something like SUM, and you see I have the different things, so there is SUMX to sum an entire table, etc. IntelliSense works just fine over here. Let's dump that out, because I really don't want to create that there. If I select a calculated field, and I right click on it, then I can go into Format, and here I can change what I want it to be as. Right now it's currency, which is correct, but I could change it to a general number, general, etc.
We'll leave it at currency. If it was a boolean, then I would come in there at TRUE/FALSE or a date, etc. The number of decimals you want to show and the symbol. The standard formatting stuff. We'll cancel on out of that, and the same thing down here for all of these, so I can change the formatting, and I can use my different functions that I have within Dax. Let's switch over to the workbook, and in the workbook up here, you see these calculated fields. Note again, there's nothing there for calculated columns, but if I click on that, and I go Manage Calculated Fields, here are my calculated fields.
If we look at one, GoalsByCategory, and I click the Edit button, then up pops my little dialog box, and here it's asking for the table where I want this calculated field to appear. We're saying this is under the LinkedTable, and what you do there is these calculations, you want to put them under the table that they're going to be used in so that it's easier for the actual user. You really want to write in a description, and your calculated field's name should be something that's explanatory.
Here's the actual code, =SUM(LinkedTable[Goal]), and if I call Check formula, I know this is difficult to see, this says, "No errors in formula." By the way, here's my formatting down in here, and we'll cancel out of that. If I look at a different one, PreviousYear, Edit, here's my calculated field named Previous Year. Previous Year lives, or that is, it's positioned on the FactOnlineSales table, and here's its actual code. Again, if I Check formula, there's no errors in the formula, and it's as a currency, so this works.
This is really excellent. Frankly, the way you want to do this, because these are very difficult to debug, the way you want to do this is build little bitty formulas, and then add them together so that I could use Previous Year calculated field into another calculated field. When you're creating Dax formulas, you want to try to reuse your code wherever you can. Let me give you an example of that. Here's Sales year-to-date, and if I click Edit, there's my code. Now I'm going to copy it just by selecting everything. I didn't select the equals sign, that's okay.
Ctrl-C, and I'm going to exit out of it. Now I'm going to call New, and this is set up for me, FactSalesOnline, and that's fine, and I'm just going to call this DemoCopy, and we'll enter it down here. Note the equals sign is already there for me. If I happen to have copied the equals sign, then we can just go through and dump it. Ctrl-V. But I don't want another year-to-date. I enter the Back key, and now it's telling me the other things I have. Let's go quarter-to-date. All I have to do is select the Q. Now I'll call Check formula. "No errors in formula." Now I immediately picked up the quarter to date.
That's because the parameters for this formula are the same as the parameters for year to date and month to date. Now I can have this in here, and we'll just rename this thing Sales QTD. Of course, put your descriptions in down here, and we want it as a currency, two's fine, and that looks good. We call OK. This error is not an error in the actual code down there. This error that popped up is a little PowerPivot error, so I'll just say OK.
It's a COM error off of PowerPivot, so we can pretty much ignore that, but then if you ever throw that error, just close the interface and go back over to Calculated Fields, Manage Calculated Fields, and there it is. If it's not there, then just repeat that. Sometimes you might have to shut down Excel and restart it. It's just one of the oddities of COM. Here we go, we've got that formula down there. This is really easy and an excellent way to build out on your formulas. Microsoft has an excellent reference library that's available for Dax online.
If you just do a search as you see here, "dax reference powerpivot," it will take you over into this on the Microsoft Developer Network, and they have examples and an explanation of all of the Dax references. Notice they're grouped, so if we come down there and look at filter functions, which are very popular, and then we look at the CALCULATE function, we can call that out, and if it gives us the syntax, and the parameters, the return values, and then examples. This is really quite excellent, and you can come through and work with your different Dax functions by examining this.
I have a diagram up to review and to show you the Dax functions and the way you write the code. The "Commision" is the name of the calculated field, and then we have a colon after that. Of course, you know this is a calculated field because you were paying attention, and you know that if I was doing a calculated column, I would not have the name, nor would I have the colon. But after the equals, everything is about the same. Now I have the equal, and then in green, I have a function, and the function we're using here is SUM.
Then I go into the open parentheses, and I have the name of the table, OrderDetails[unitprice]. You need to use the bracket, by the way, whether or not you have any whitespace, if you any of you are wondering whether or not you need to use it, and remember, IntelliSense is going to help you. Then you come through, and I've got the closed parentheses, and then I have an operator, which is a multiplier, times a value, which is 0.3. What's that going to do? You've got it. We're going to sum from the OrderDetails table, unitprice, and then we're going to multiply it times 0.3.
Couple of things that you might consider. When you're developing calculated fields or calculated columns, try to create dependent calculated fields. That is, develop your code in steps, using previous steps in the next step, rather than long, sophisticated, and difficult-to-debug formulas. When using a calculated column, you can set recalculation to manual. However, if the data changes, the results will be incorrect until you update, which is another reason to use calculated fields.
PowerPivot will not prevent you from deleting relationships that are necessary between tables for a formula to work, nor will it warn you that you are breaking a certain Dax calculation, at least in this iteration it won't. You need to carefully document your Dax to prevent this. Hopefully, future iterations of the product will provide you with warnings to assist your development. PowerPivot will help you, however, with a circular dependency in a formula and throw an error, and when it throws an error, it will show up in either, if we were using the interface in Excel, it will say, "There's a mistake in the code," or if we're doing it over in the data model, we get this little error with a pop-up.
Let me show you what I mean by the error checking. If we take our friend Quarter To Date that we created, and I'm just going to move in here, and I'm going to delete out the comma. Now I'm going to call Check formula, and if we read the error message, "The function used in this expression "is not a valid function nor the name of a measure." OK. Maybe not the most helpful error message you ever had, and there certainly is no pointer to give you a hint, like perhaps you're used to in some other interfaces within Microsoft, but it will tell you there's an error.
Again, this should give you a hint that you need to go through and create small little calculations and then build on them. You just would build on them by using the name of the calculated field into another calculated field. This same error message would show up if we were changing the code down in PowerPivot. I switched on over to the data model, and I have highlighted our code, Sales QTD, and see the very helpful $210 million, whatever it is? This is one of the advantages of doing your code here, though I personally prefer to do it in the other interface.
Again, if I come in here and move that on out and come off of the row, there we go. Now I have that error, and if I look at this, you see I have the same error pop up, so it's telling me, I've got my error in here, and again, if I come back over to it and fix it, if I click in the right place, and when I fix the error by reinserting the comma up here and then recalculating, then it goes away. You see, you can detect the errors in either this interface or again, when we were over in Excel, we can do the same thing in this interface.
It just seems to me to be easier in this interface, because when I call Check formula, I get the pop-up. That's personal preference.
- Data modeling concepts and terminology
- Exploring the Power Pivot interface
- Adding data sources
- Working with date tables
- Understanding perspectives and key performance indicators (KPIs)
- Getting started with DAX syntax
- Understanding evaluation context