Using DAX to Build Measures in Power BI
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.
- 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
This course is intended for anyone who wants to learn how to use DAX to build measures in Power BI.
To get the most out of this course, you should have a basic understanding of Microsoft Power BI.
An additive measure like Sum accumulates values over a dimension. A non-additive measure like a distinct count doesn't. A semi-additive measure can accumulate values over some dimensions but typically not time. The main complexity with a semi-additive measure is identifying a point in time value. Closing balances or current stock holdings are examples of semi-additive measures.
In this demonstration, I will use sales data from one customer but treat it like an account balance, so that will require a small leap of faith on your part. To make it more believable, I will rename the sales measure to balance. Next, I'll remove the date field from the date hierarchy and use the ClosingBalanceMonth function to retrieve the end-of-month balance. This is very simple, the expression is the balance measure, and I'll use dates from the FiscalYear date table. 0K, not what I expected, but there is a logical explanation. I'll add the date column back into the hierarchy, and we can immediately see that ClosingBalanceMonth grabs the value from the last day of the calendar month. If there is no value for that date, then ClosingBalanceMonth returns blank. This will obviously need some refinement. But before I do that, I'll adjust the ClosingBalance precision. Instead of using ClosingBalanceMonth, I'll use calculate with the Time intelligence function LastNonBlank as my filter. LastNonblank takes a column in which the expression, in this case, balance is not blank. Excellent, now we have closing balance figures for each month. Let's drill down to the date level and see how that looks. Oh, the closing balance is now evaluated in the context of a day. When viewing the data by day, we want the closing balance column to be the same value for each day of the month. All of July should be $205, and all of August should be $712. I'll just rename closing balance to closing balance EOM – end of month, so we know what we are talking about. I need a way for LastNonblank to work within the context of the whole month, even when we are looking at the data using rows of days. This can be achieved using the ParallelPeriod function, perhaps in a way not entirely intended. Because I want LastNonBlank to look at all dates within the current month, my interval will be Month, but the offset is zero. Not the month before, not the month after, but the parallel period is the current month. You could think of this as a context within a context. As we move through each day row of the month parallel period is always returning the LastNonBlank value of the month the current date belongs to, even though we are looking at the data in the context of date rows.
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.