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.
To explore more Kubicle data literacy subjects, please refer to our full library.
Lesson Goal (00:04)
The goal of this lesson is to learn how DAX can be used to improve your ability to analyze data in Power Pivot.
Understanding DAX (00:12)
Power Pivot allows you to analyze your data using tools such as Pivot Tables, Pivot Charts, and 3D Maps without having detailed technical knowledge. To conduct more advanced analysis of a Power Pivot data model, you need to understand DAX. DAX, or Data Analysis Expressions, is the formula language used in Power Pivot.
Although some DAX formulas have names that are similar to Excel functions, DAX formulas work differently to Excel formulas due to the differences in how data is stored in Excel and Power Pivot. Excel data is stored in an unstructured format, and formulas are applied to cells or ranges of cells. Power Pivot data is stored in an organized, tabular format. DAX formulas are applied to tables or to columns from these tables.
Course Introduction (01:47)
This course contains two main components. The first outlines when you would use DAX formulas in Power Pivot, such as when creating a new column or when creating a measure. The second introduces the simpler and more commonly used functions in DAX.
The data set used in this course relates to the sales of a pharmaceutical company to customers located in the UK. Each row represents the monthly sales of an individual product to an individual customer. Each row details the quantity of the product sold, and the revenue generated from the sale. It also contains geographical information on the customer.
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.