DAX Data Types and Other Aspects (Checking & Handling Errors)

DAX (Data Analysis Expressions) is used as a formula expression language in Analysis Services, Power BI, and Power Pivot. DAX data types and formulas contain operators, functions, and values to execute calculations and queries within tables and columns of other data models.

In that article, we demonstrate DAX data types and other aspects. Also, we explain the working principle of DAX data types.

Download Working Excel File

Download the working file from the link below.

DAX Data Types and Other Aspects in Power Pivot

DAX expressions are common in various data analysis tools. In the below sections, we will discuss data analysis expressions and their types in Power Pivot tables.

DAX or Data Analysis Expressions

It is well known that Excel functions manipulate cells or ranges. But DAX does not support the concept of dealing with cells or ranges as an 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. And it’s important to mention that users can’t jump into a specific row.

For example, suppose that we have two worksheets that include sales data for 10 employees.

• Select range B3: H12 > Copy it (CTRL+C) > Power Pivot > Click on Manage (Data Model section).

• The Power Pivot for Excel window pops up. Click on Paste (inside the Clipboard section).

• Provide a suitable name for it. Afterward, click on the OK You can see that data will be pasted into PowerPivot.

Go through This Article to add Power Pivot along with other Excel tabs.

Operations in the Power Pivot Table

• Firstly, click on the drop-down arrow, and you will see all the column headers as listed. In this case, we select Gender, and therefore the 3^rd column is highlighted.

• For Filtering, click on the drop-down arrow in the Gender column and then tick F. You will see that all rows having values equal to F in the Gender column will filter.

• The new formula execution is pretty simple in Power Pivot. Highlight the Add Column at the end of the table and then insert “=[“in the formula field. You will get a dropdown list. Then select the column that you want to manipulate. In this, select Quarter 1 and so on to complete the entire formula as
  =[Quarter 1] + [ Quarter 2] + [Quarter 3] + [Quarter 4]
  

• After formula insertion, Power Pivot names the new column CalculatedColumn1. You can rename the column by right clicking on it.

DAX Operators

The below table shows the different types of DAX operators. They are similar to those for Excel worksheets.

DAX Data Types

DAX supports many data types. Data transforms into one of the tabular model data types when it imports into a model. In the previous example, we showed how PowerPivot handles numeric values. In fact, there are different numeric types such as Integer, Real, Currency, Date (Date and Time), TRUE/FALSE (Boolean).

• Therefore, the following image shows data from the worksheet.

• After fetching the data and executing some operations, the outcome looks like the picture below.

You can see that “9/13/2022” is replaced with “9/13/2022 12:00:00AM”. That is because PowerPivot stores dates in DateTime format. And DAX data operators execute different operations just fine.

Syntax for Referring to a Column

There are two ways to refer to a column within Power Pivot. They are:

(i) ‘Table Name’[Column Name]

(ii) TableName[Column Name]

Column names should always be enclosed in square brackets. Also, single quote encloses the table name. But most of the time, users can omit the quotes if the name does not contain any special characters. Power Pivot searches the columns within the current table if users omit the table name. In order to avoid any confusion, the best practice is- you always specify the complete name, including both the table name and the column name.

• The below shows how Excel 365 references the columns 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, IntelliSense doesn’t display the table names. Therefore, you need to type at least the first letter of the table name to display the complete name, which includes the table name. Since the table name “Sales” begins with “Sa”, Excel displayed all columns from the Sales.

Checking and Handling Errors

Sometimes, we may get errors when trying to use DAX to create a new calculated column.

• Firstly, if we try to add columns for Gender and Age, Excel will return #ERROR as shown in the latter picture.

• In this case, click Ctrl (beside Calculated Column)> Show Error to open the PowerPivot for Excel dialog box. As a result, the box will give you a warning message. And you can update your formula based on this warning message.

Conclusion

This article demonstrates the basics of Data Analysis Expressions’ types and its other aspects. We hope this article sheds enough light on DAX types to begin with as a beginner learner.

Do check out our awesome website, ExcelDemy, to find interesting articles on Excel.