1. Introduction to DAX in Power BI
Hi, I'm Jess. I will be your instructor for this introductory course on DAX. We'll cover the basics of DAX and have you writing DAX calculations before you know it.
2. What is DAX?
DAX stands for Data Analysis Expressions.
It is a formula language used in Power BI that provides the ability to create columns, tables, and measures.
If you've ever used Excel formulas and functions, good news! DAX is to Power BI, what formulas are to Excel. For example, the SUM function is the same in Excel as it is in DAX.
DAX is more than just Power BI. It is used in other Microsoft tools including Analysis Services and Power Pivot.
3. DAX functions
Functions are predefined formulas that perform calculations on specific values, called arguments, in a particular order.
Each function has a specific syntax indicating the order of arguments to be expected.
DAX has over 200 different functions that fall into several categories, including Aggregation, Date and Time, Logical, Text, and many more. You can find a list of all functions and their syntax in Microsoft's documentation, linked here.
4. DAX functions example
Let's take a look at two examples, starting with the SUM function.
The SUM function takes a column and adds up all the numbers. It has one argument. In the example, we are summing the sales column.
The LEFT function takes two arguments a string and an integer. It returns the specified number of characters from the start of the string. In our example, we input "DataCamp" and specify 4 characters, which returns "Data".
5. Creating calculated columns
Calculated columns can be very useful as they enable us to expand our existing dataset without editing the source data. Calculated columns evaluates at a row level and therefore adds a new column to an existing table.
Any column that is created is calculated at data load or when the data is refreshed.
6. Creating calculated columns
Let's say we are trying to calculate price with tax. For this, we start by defining the column name followed by an equals and proceed to write our formula. In this case Price + (Price * Tax).
7. Creating calculated columns
In the table below, we can see this implemented in a real scenario.
8. Creating calculated measures
Calculated measures works a bit different. Measures enable you to create complex calculations that can be used on your data.
Where columns evaluate for each row, measures aggregate multiple rows and adds a new field that can be added to a vizualisation.
The measure itself is calculated at query time rather than when data is loaded, therefore gets updated as you interact and filter the data. This means it is more efficient because the calculation isn't run every time a table is accessed, instead only when it's being used.
Measures can be created in two ways, by writing a DAX formula or by using Power BI's Quick Measure. We will get into Quick Measures later on in the course.
9. Creating calculated measures
Let's go back to our previous example and calculate total price with tax that uses the SUM function to calculate the SUM of all prices with tax together. We can now add this measure to a card in a Power BI report to discover the Total price with tax is $185.
10. Summary
To summarise - calculated columns evaluate for each row. It adds a new column to an existing table and is calculated at data load or when the data is refreshed.
11. Summary
Whereas measures aggregate multiple rows. Results in another field that can be added to your visualization and is calculated at query time as you interact and filter.
12. Summary
In later chapters, we will dive into calculated tables.
13. Adventure Works
In our exercises, we'll be working with data from the fictional bike company Adventure Works that sells bike and bike-parts globally.
We will have a table called Sales which contains information about every order item purchased.
14. Let's practice!
Let's practice!