Course Duration: 1 day
Overview:
DAX, or Data Analysis Expressions, is the language that’s used to create formulas for Power BI that extend our data model. DAX gives us the ability to create additional information at runtime so that we can quickly and easily generate new information beyond the information that’s in our model already.
If you already know how to build data models in Power BI Desktop, DAX is a logical next step, because there are things that we can do with DAX that we can’t do as easily or at all without it. This course is designed to explore the capabilities of DAX formulas in Power BI.
Pre-requisites:
This is not a beginner’s course in Power BI. Our assumption is that you know some things before attending this course. For example, you know how to connect to data sources in Power BI Desktop, and once you have it in Power BI, you know how to add and remove columns, and to change data types for example.
You would know how to create simple visualisations, including charts and tables, or you are already a Power BI user with a basic understanding of DAX. If not, we recommend attending the 2-day Power BI Introduction course first.
Subject Areas:
- Set Up the Data Model
- Data modelling: The basics
- DAX filter context
- Get data for your model
- Check relationships
- DAX Aggregate Functions
- Create a SUM measure
- Understanding DAX syntax
- Display the measure in a matrix
- COUNT and COUNTROWS measures
- Check data with COUNTBLANK
- Create a DISTINCTCOUNT measure
- Measure Editing Options
- Format a measure
- Delete or edit a measure
- Make DAX easy to read
- Add comments to a measure
- The quick measures option
- Move a measure
- Measures and Calculated Columns
- Add a calculated column
- Explicit vs. implicit measures
- Create a measure with operators
- Use DIVIDE for percentages
- DAX Logical and Filter Measure
- Create a FIND calculated column
- Create an IF calculated column
- Use OR to handle font casing
- The CALCULATE Measure
- Use CALCULATE to filter data
- Advanced filtering
- Use CALCULATE over multiple tables
- The ALL Measure
- Create an ALL measure
- Use ALL as a filter
- Use ALL in percentage measures
- Pass a table or column to ALL
- Use ALLSELECTED as a filter
- Iterators and Row Context
- Anatomy of Iterators and row context
- Iterators SUMX and AVERAGEX
- Create a SUMX measure
- Further examples of SUMX
- Create an AVERAGEX measure
- Create a RANKX measure
- Date and Time Measures
- DAX date and time functions
- Compare historical monthly Data
- Create a DATEDIFF measure
- Compare dates cumulatively
- Create TOTALYTD/MTD/QTD measures
- Create a DATEADD measure
- Run cumulative totals
- Nesting FILTER, ALLSELECTED and MAX for date analyses
- Create cumulative visualisation
