1. DAX for creating tables and columns
Hello, I'm Carl and I'll be one of your instructors in this course about DAX.
2. DAX stands for data analysis expressions
DAX is a formula expression language used in multiple Microsoft tools such as Power BI and PowerPivot. DAX formulas include functions, operators and values to perform advanced calculations and queries. It's used in measures, calculated columns, calculated tables, and even for row-level security. Let's focus on first three in this chapter.
3. The power of DAX
What makes DAX so powerful?
First, it opens up many new functionalities. You can use joins, filters, measures and calculated fields to perform complex calculations and create visualizations not possible before.
Combining DAX and Powery Query together provides a powerful data analysis tool. You can dive deeper into the data to extract key insights and use DAX for rapid prototyping.
4. Measures vs calculated columns
In DAX, it's important to understand the difference between measures and calculated columns. In calculated columns, DAX computes a value for each row when importing data. In this example, we calculate the cost for each row by subtracting the profit value from the sales value.
5. Measures vs calculated columns
Measures are useful to aggregate values and when you don't need a column added to your data model. In this example, this measure calculates the sum of all sales. Depending on how we segment the data or use filters, we can see the sales per region.
6. Context allows you to perform dynamic analysis
DAX calculations change depending on how the context is set. There are three types of context: row, query, and filter context. Because of that, it's crucial to understand how different contexts work.
Context enables dynamic analysis, where the results of a formula change to reflect the cell selection. For instance, if you exclude a Sales region from a dashboard, the total sales will change. The results of calculations will change depending on how you use the row, query, and/or filter context.
7. Context allows you to perform dynamic analysis
Row context is the simplest and evaluates at the row level. It includes the all values in the current row; therefore DAX formulas for calculated columns evaluate at the row context. The calculated column from before is an example of row context.
8. Context allows you to perform dynamic analysis
Query context refers to the subset of data returned by a formula, which can be changed by slicers, page filters, table columns, row headers, and interactions with visualizations. It applies on top of row context.
Query context is what we mainly used in the introductory Power BI course. Our visualizations changed by adjusting a filter on the report pane or selecting elements of a report.
9. Context allows you to perform dynamic analysis
Here, we can change the query context to be at the region or state level to see these two different results.
10. Context allows you to perform dynamic analysis
Filter context is the set of values allowed in each column, or in the values retrieved from a related table. It can be applied through arguments in a formula or by using report filters on row and column headings. This context applies on top of query and row context, meaning its effects are last.
11. Context allows you to perform dynamic analysis
Here, there's a DAX formula using CALCULATE() that filters on the East region. We'll go deeper into context filters during the exercises.
12. Context in a nutshell
It's crucial to understand how different contexts will impact your visualizations. Calculated columns evaluate data at the row level, while you can use different filters and slicers to change visualizations in your report pane. This is the query context.
Finally you have the filter context, as seen in the previous slide with the CALCULATE function, which can use arguments in a DAX functions to filter for fields like region.
13. World wide importers dataset
We'll be using the World Wide Importers Data Warehouse in the exercises. This is a fictitious dataset about the imports and distribution of novelty goods of a wholesaler. It mimics a typical database you'd find in the real world.
There's one fact table containing sales transactions, and multiple dimensional tables detailing dates, customers, cities, employees, and items.
14. Let's practice!
It's time to start your journey on becoming a DAX master! Enjoy!