Sign in or start a free trial to avail of this feature.
1. Introduction to DAX
In this lesson, we will introduce DAX, discuss why Power Pivot needs its own formula language and introduce the dataset for this course.
What is DAX
- Data Analysis Expressions, or DAX, is the formula language used in Power Pivot
- When conducting analysis in the Excel data model, you use DAX formulas
Why does Power Pivot not just use normal Excel formulas?
- Normal Excel data is generally in an unstructured format
- Excel formulas are designed to work with cells or ranges of cells
- However, the Power Pivot data model is made up of structured tables and columns
- DAX is designed to work with this structured data model
In previous courses, we introduce Power Pivot, which allows you to bring together data from various sources and create a data model in Excel. We saw that you can analyze data and produce outputs such as pivot tables and Power View sheets without a large amount of technical knowledge. However, if you want to conduct more advanced analysis of your data model, you'll need to use DAX formulas.
In the next few courses, we'll introduce all the fundamental concepts of DAX and show you how to use it in Power Pivot.
First, let's answer the question, what is DAX? DAX or data analysis expressions is the formula language used in Power Pivot.
Any calculations in Power Pivot will use DAX formulas.
You might wonder why Power Pivot needs it's own formula language and can't just use the normal Excel formulas.
This is because data in Excel has no rigidly defined structure and formulas work on cells or ranges of cells.
By contrast, in Power Pivot, all the data in the data model has a clearly defined structure.
The data is stored in various tables and the tables are made up of fields which are stored in columns with a defined type.
As a result, we need formulas that are designed to work with tables and columns instead of cells.
Although the structure of formulas is slightly different between Power Pivot and Excel, many of the actual functions such as sum and average are common in both applications.
If you have a good knowledge of Excel formulas, you'll be familiar with many of the DAX formulas we'll work with.
In this course, we'll explain when DAX is used in Power Pivot and we'll introduce some of the simplest and most common DAX functions.
The course will have two main sections. In the first few lessons, we'll show you the various applications of DAX including calculated columns and measures and learn about adding tables to Excel's data model.
After that, we'll start looking at DAX functions.
We'll start with aggregate functions, then move on to other common categories of DAX functions like logical functions and text functions.
We'll use data from a pharmaceutical company selling products to various customers in the U.K.
As we look at this dataset, we can see that each row contains sales records of an individual product to an individual customer along with geographical data related to the customer.
We also have data on the quantity of the product sold and the monetary value of the sale.
Sales are recorded on a monthly basis.
Our initial data model will contain one table showing this pharma data, as well as a date table which will allow us to look at time intelligence functions.
In the next lesson, we'll start learning how to use DAX by looking at calculated columns and measures.