[Editor’s Note: This is an introduction to DAX (Data Analysis Expression). The author has covered from very basic like how DAX works, DAX data types, DAX operator. At the end of the article, she has covered how to check and handle an error with DAX]
Last time, I made an introduction on how to import data into PowerPivot, create PivotTable/PivotChart using PowerPivot. In that article, I also elaborated on how to insert data slicers, how to modify PivotTable/PivotChart, and even how to conditionally format them. Today I’d like to give you an introduction on DAX – Data Analysis Expressions. It is PowerPivot’s syntax for defining calculation expressions. DAX is similar to Excel function. But it allows you to create more advanced calculations when comparing with Excel function.
How does DAX work?
It is well known that Excel functions manipulate cells or ranges. But DAX does not support the concept of cell or range as Excel function does. It only uses the concept of columns or tables. And you can get the value of a column for a single row or for the whole table. Here I have to remind you that you cannot get access to a specific row.
For example, suppose that we have two worksheets that include sales data for 10 employees.
Now let’s copy data from the worksheet into PowerPivot. Select range A1: G10 and then click on POWERPIVOT. In the prompted PowerPivot, click on Paste and Excel will prompt Paste Preview dialog box. In the Paste Preview dialog box, fill 2013 in the Table Name field. After you click on the OK button, you can see that data will be pasted into PowerPivot.Use a similar approach to copy data from worksheet named 2014 into PowerPivot too. Look at Figure 1.3, you can see that there are two tables in PowerPivot for Excel now.
If you click on the drop-down arrow, you will see all the column headers as listed. In our problem, we selected Gender and therefore 3rd column was chosen.
Click on the drop-down arrow in the Gender column and then select F, you will see that all rows having values equal to F in the Gender column will be filtered.Select AddColumn column at the end of the table and then fill “=[“ in formula field. You will get a dropdown list as shown in the top panel of Figure 1.6. Then select the column that you want to manipulate. In our problem, select Quarter 1 and you will get “=[Quarter 1]” in formula field. Then use the same approach to complete entire formula “=[Quarter 1] + [ Quarter 2] + [Quarter 3] + [Quarter 4]”. After you completing formula, you will get something similar to the middle panel of Figure 1.6. In the new CalculatedColumn1 column, the values equal to the sum of values in those 4 columns before this new column. Finally right-click on CalculatedColumn1 header (in the red box) and then fill into Total, you will get a new column – Total.
DAX Data Types
In the previous example, I have shown how PowerPivot handle numeric values. In fact, there are different numeric types such as Integer, Real, Currency, Date (DateTime), TRUE/FALSE (Boolean). The left panel of Figure 2.1 shows data from the worksheet and the right panel shows the same data imported into PowerPivot and corresponding calculated columns.You can see that “2011/12/13” was replaced with “2011/12/13 0:00:00”. That is because PowerPivot stores date in a DateTime data type. Internally, it is a floating-point number, wherein the integer corresponds to the number of days (starting from Dec 30, 1899) and the decimal corresponds to the fraction of the day. In other words, hours, minutes, and seconds are converted to decimal fractions of a day. Current time is 2016-12-17T16:51:26 and therefore formula Now()+1 can return 2016/12/18 16:51:26.
Let’s look at the first row. Formula =’Data Type'[num1]+’Data Type'[num2] returns 6 while formula =’Data Type'[num1] & ‘Data Type'[num2] returns 15. Both of them were calculated base on the same source data. But we got different results. This is because the resulting value depends on the operator and not on the source columns. Source data was treated as string data when using operator “&”.
The below table shows the different types of DAX operator. They are similar to that for Excel worksheets.
(5+2)* 3 = 21
5+2*3 = 13
|Arithmetic||+||4+2 = 6|
|–||4 -2 = 2|
|*||4*2 = 8|
|/||4/2 = 2|
|Comparison||=||[Name] = “Jack”|
|<>||[Name] <> ‘Jack’|
|>||[Num] > 0|
|>=||[Num] >= 0|
|<||[Num] < 0|
|<=||[Num] <= 0|
|Concatenation||&||“Jack is” & “17” will return “Jack is 17”|
Syntax for referring to a column
There are two ways to refer to a column – ‘Table Name’[Column Name] and TableName[Column Name]. Column names should be always enclosed in square brackets. The table name can be enclosed in a single quote character. But for most of the time, the quotes can be omitted if the name does not contain any special characters. The column will be searched within the current table if the table name is omitted. In order to avoid any confusion, I suggest that you always specify the complete name including both table name and column name.
Figure 3.1 shows that how the columns are referenced in Excel 2013 by default. The thing surrounded by a red box is called IntelliSense. It is helpful and will show all the possible column names and references that you can use in a formula.
By default, the table name will not be displayed by IntelliSense. Therefore, you need to type at the least the first letter of the table name to display the whole complete name which includes the table name. Since both table name “2013” and “2014” begins with “2”, Excel displayed all columns from all of these two tables.
Check and Handle Error
Sometimes, we may get errors when trying to use DAX to create a new calculated column. For example, if we try to add column Gender and Age, Excel will return #ERROR as shown in Figure 4.1.
At this time, you can click on the symbol surrounded by the red box in Figure 4.1 to open PowerPivot for Excel dialog box. The box will prompt you a warning message. And you can update your formula based on this warning message.
Download working file
Download the working file from the link below.