DAX in Power BI
This training introduces Data Analysis Expressions (DAX), a formula expression language used to define calculations in PowerPivot for Excel® workbooks, tabular model projects authored in SQL Server Data Tools and in Power BI Desktop. DAX functions provide extensive filtering to calculate on data across multiple tables, work with relationships, and perform dynamic aggregation.
There are millions of Microsoft Excel users who are familiar with using Excel formulas to perform calculations. Those calculations may be as simple as adding up a column of numbers, or they may be far more complex simulations of various business models. But in every case, each formula is built using a combination of basic operators and functions that are provided within Excel. Similarly, for professional BI solution developers using Multidimensional Expressions (MDX) in multidimensional model solutions, the fundamental concepts of creating calculations by using formulas is much the same; however, the differences in syntax, operators, and functions between MDX and DAX formulas is greater than it is between Excel and DAX formulas. Whether you are familiar with Excel or MDX formulas, this basic understanding of formulas is important when developing tabular data modeling solutions. While understanding Excel or MDX formulas will give you a head start in learning DAX, it is not necessary.
DAX formulas are very similar to Excel formulas, and there is considerable overlap between the list of DAX functions and Excel functions. But there are also significant differences, and many new functions in DAX that don’t exist within Excel. These functions are designed to offer capabilities that focus on data analysis, particularly for related tables of data, and for dynamic analysis.
- Knowledge of how to create content using Power BI Desktop.
- Knowledge of how writing formulas in Excel
Outline: DAX in Power BI
Module 1: Introducing the Power BI Desktop model structure, star schema design basics, analytics queries, and report visual configuration.
- Describe the structure of a Power BI Desktop model.
- Explain star schema design basics.
- Define the term analytic query and its phases.
- Describe how fields can be used to configure a report visual, which then generates an analytic query.
Module 2: Writing DAX formulas to create calculated tables, calculated columns, and measures.
- Describe the different DAX calculation types.
- Write DAX formulas.
- Describe DAX data types.
- Work with DAX functions.
- Use DAX operators.
- Use DAX variables.
Module 3: Add calculated tables and calculated columns to your data model.
- Create calculated tables.
- Create calculated columns.
- Identify row context.
- Determine when to use a calculated column in place of a Power Query custom column.
- Add a date table to your model by using DAX calculations.
Module 4: Work with implicit and explicit measures and recognize the similarities of, and differences between, a calculated column and a measure.
- Determine when to use implicit and explicit measures.
- Create simple measures.
- Create compound measures.
- Create quick measures.
- Describe similarities of, and differences between, a calculated column and a measure.
Module 5: Introducing DAX iterator functions
- Describe iterator functions.
- Use aggregation iterator functions.
- Calculate rankings. Module 6: Introducing filter context, which is used to evaluate measure formulas.
- Describe filter context.
- Use the CALCULATE function to modify filter context.
- Pass filters to the CALCULATE function.
- Pass filter modifiers to the CALCULATE function.
- Perform context transition.
Module 7: Adding DAX time intelligence calculations to your model.
- Define time intelligence.
- Use common DAX time intelligence functions.
- Create useful intelligence calculations. Module 8: Introducing DAX Calculation Groups
- Define Calculation groups.
- Define Calculation Items
- Configure Formatstrings
- Configure Calculation Group Properties