Augment your Power BI data model with custom DAX measures to improve efficiency and provide better metrics for your users. Power BI comes with an extensive range of built-in functions that assist with many common report scenarios. But sometimes, you'll need to create your own measures where the "out of the box" ones don't do exactly what you want or exhibit unintended behavior.
In addition to scenario-specific functions, Power BI has general-purpose functions that you can use in DAX statements to create complex custom measures. This course explores the use of specific and general functions with DAX to address several scenarios.
Learning Objectives
- Learn how to replace numeric columns with measures that use SUMX and CALCULATE functions
- Learn about Time Intelligence functions and how to make year-on-year and month-on-month reports using time-based functions and custom DAX measures
- Learn how to use Time Intelligence functions and custom DAX to create semi-additive point-in-time measures
Intended Audience
This course is intended for anyone who wants to learn how to use DAX to build measures in Power BI.
Prerequisites
To get the most out of this course, you should have a basic understanding of Microsoft Power BI.
Standard or out-of-the-box measures can improve report performance by replacing column data, thereby reducing dataset size and providing extra insight. Standard measures can only get you so far, but there is almost no limit to what can be achieved by creating custom measures using DAX functions. While the DAX function library is extensive and has many functions designed to solve specific problems, general functions like calculate can be used in many different scenarios. Calculate which takes an expression as its first argument and at least one filter as its second argument encapsulates what Power BI is all about: evaluating expressions in a filter context.
Most reporting scenarios involve time series, seeing how values evolve and change over time. DAX is loaded with Time intelligence functions designed to assist in this area. There are specific functions for aggregating over different time intervals and for comparing past and present intervals. One grey or shady area is semi-additive measures. While these measures can be aggregated over some dimensions, time is not one of them. Power BI has some built-in functions like closing and opening balances to identify point-in-time values, but the results are not always as expected. In these situations, you can create custom DAX measures using generic functions like Calculate and ParallelPeriod.
My name is Hallam Webber, and I've really enjoyed presenting this course and exploring how to solve some of the more tricky issues that can crop up when using DAX statements and functions. I hope you've enjoyed it too and learned something new and valuable.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.