Power BI: DAX Advanced

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.

Evaluation Context

• What is Evaluation Context?
• The difference between evaluations using Row Context and evaluations using Filter Context.

 CALCULATE Function

• Recap on the CALCULATE function.
• Using complex filters within CALCULATE using FILTER.

 Removing Filters

• 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

Using Variables

• 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

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.