This course will cover the ability to use Data Analysis Expressions (DAX) language to perform powerful data manipulations within Power BI Desktop.
Learning how to create effective DAX formulas will help you get the most out of your data. DAX is a powerful language for manipulating Power BI data, but it can be a challenge to get the calculations you want. Understanding DAX is the key to getting the information you need so you can begin to solve real business problems that affect your bottom line. This is Business Intelligence, and DAX will help you get there.
What you'll learn
- What is Evaluation Context?
- The difference between evaluations using Row Context and evaluations using Filter Context.
- Recap on the CALCULATE function.
- Using complex filters within CALCULATE using FILTER.
- Using ALL, ALLSELECTED & ALLEXCEPT Functions.
VALUES & DISTINCT Functions
- The difference between VALUES and DISTINCT
- Looking at the “Blank” entry.
- Using ALLNOBLANKROW
Activating Inactive Relationships
- The problem of Inactive Relationships
- Using the USERELATIONSHIP function
Working with Many-to-Many Relationships
- Understanding “Conventional” Many-to-May relationships between Dimensions.
- Creating a “Bridge Table”.
- Using the CROSSFILTER function.
- Looking at Many-to-Many based on Cardinality.
- Understanding Granularity
- Looking at the TREATAS Function.
- Understanding “Weak” relationships
- The VAR and RETURN statements
- When to use Variables
Advanced Filter Context
- Understanding Context Transition
- Understanding Expanded Tables.
Advanced Calculated Columns
- Problems with Circular Dependencies
- Using the EARLIER Function.
Advanced Table Functions
- Using SUMMARIZE
- Using CALCULATETABLE
Putting it All Together (Time permitting)
This session is a “workshop” that explores using DAX to solve a variety of data analysis problems, for example:
- Finding TopN Percent
- Binning Measures into numeric categories
- Comparing data in 2 different time frames
- Finding New and Returning customers
- Of the customers who bought X, who also bought Y
To get the most from this course
We recommend that you attend our 1-day Microsoft Power BI Introduction to DAX course prior this course. Otherwise you should have some experience of DAX and its calculations and want to take your knowledge to the next level.