Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Excel 2010: Pivot Tables in Depth, author Curt Frye provides comprehensive, hands-on tutorials on Excel PivotTables, including more advanced techniques such as using macros and the new PowerPivot add-in. The course shows how to connect and consolidate data sources to power PivotTables, sort and filter records, display data in a PivotChart, print tables and charts, and also introduces the DAX language for performing advanced summaries in PowerPivot. Exercise files are included with the course.
Like Excel, the DAX language uses operators to summarize and compare values. You'll probably recognize the Arithmetic operators from your regular Excel formulas, but some of the other operators might require some explanation. In this movie, I'll start out with an overview of the operators you can use and then show you some examples of expressions with a few of them. DAX operators are divided into several different groups. The first is the Arithmetic and it's almost certainly familiar to you. You have the plus sign for addition, minus for subtraction, or to indicate a negative number, the asterisk for multiplication, and the forward slash for division.
Then there are the Comparison operators, and you probably would have used these if you created a filter or a conditional format inside of Excel. Equal tests for Equal To, then there is Not Equal To, Greater Than, and so on. Then the last two groups are the Text and Logical groups. So for Text you have the ampersand which you can use to concatenate two strings and you can use that in Excel as well. The last group is the Logical group and those allow you to test for conditions.
So for example, you have two ampersands back to back. That is the AND condition, which returns a value of true if both conditions are true. So for example, if you are looking at a value and you want to test if the sale was from 2008 and the month of January, then you could use the double ampersand operator to create an AND condition. Two vertical bars create an OR condition and it is an inclusive OR which means that either one of the expressions or both may be true for the OR expression to return true.
Then finally you have the exclamation point which is the NOT operator. The NOT operator checks to see whether something is not true. In other words if you were looking for sales that were not in 2008, you could type an exclamation point and then 2008. Now, I'll switch back to my DAX Operators workbook and launch the PowerPivot module so you can see me working with these operators in a few formulas. Now that I'm back in the workbook I'll click the PowerPivot tab on the Ribbon and then PowerPivot Window to launch PowerPivot.
Now we'll create a few examples. So for example, I will create a formula in the Add Column area. Type an equal sign. And in this case, I'll do a multiplication of multiply quantity by price. So I'll type a left square bracket, then a q, and then press Tab to accept the Quantity field, then an asterisk to multiply, left square bracket, and we'll multiply it by Price and that is the highlighted field. So I'll press Tab and then press Enter.
Now, let's say that I want to use concatenation to create a statement of the form that store blank ordered a quantity of bottles. So, I'll be using Store, StoreID and Quantity. To do that I will scroll over to the right, and then in the new Add Column column type an equals sign, and then I'll create the following formula in the Formula Bar. Double quote, Store, and then a space followed by another double quote, space, ampersand, space, then a left square bracket, and St which gives me StoreID.
So I will press Tab to accept that field. Then an ampersand, space, double quote, space, ordered, space, double quote, space, ampersand, and then left square bracket after a space. Then we have Quantity, so I'll type a q and then Tab to accept the Quantity field, a space, ampersand, space, double quote, space, bottles, period, and then a double quote.
So my goal was to have the literal text string of Store followed by a space, then the StoreID followed by a space, then the word ordered, then a space, then the Quantity ordered, then a space, and then the word bottles with the period. So hopefully, if I've created my formula correctly and I press Enter, that is what will appear in the new column. So I'll just adjust the width of the column and we can see that I created the expression correctly.
With these examples in mind, I think you'll have a good idea of how to use DAX operators in your PowerPivot expressions. Feel free to play around, but always do so in a backup copy of your data.
There are currently no FAQs about Excel 2010: Pivot Tables in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.