The ALL function
The ALL function instructs the surrounding function to ignore the execution context for the table or column within the function.
What if you wanted to know what percentage of gross revenue each product provided? It should add up to 100% however you sliced and diced your data.
If you simply divide your gross revenue for a product by the sum of gross revenue for all products, you can get that answer. But the filter context will make that result 1:
% Product Gross Revenue =
DIVIDE( SUM(Sales[Gross Revenue]), SUM(Sales[Gross Revenue]) )
You can use CALCULATE with ALL to ignore any context filters:
% Product Gross Revenue =
DIVIDE(
SUM(Sales[Gross Revenue]),
CALCULATE(SUM(Sales[Gross Revenue]),ALL(‘Product'[Product Name]))
)
If you use the Product Name column from the Product table, this will work.
Figure 7.7 – With CALCULATE and the ALL filter option, you can ignore filter context
You can use CALCULATE to change the filter context of a value or even ignore the filter context entirely.
Next, we’ll look at a special kind of calculation – time intelligence calculations, which Power BI has built-in capabilities for to ease implementation.
Implementing time intelligence using DAX
Most reports have a time quality; we often need to compare now versus some previous point in time to see whether we are trending in the right direction. Power BI offers many powerful time intelligence functions to handle this kind of reporting.
In Chapter 5, Designing a Data Model, we covered how to create a date table. This chapter explains how to use its awesomeness.
Time intelligence functions fall into one of two types:
- Functions that require CALCULATE.
- Functions that return a scalar value on their own. This is sometimes referred to as syntactic sugar, which is an awesome name.
A function that uses the DATESYTD call requires CALCULATE:
Products Ordered YTD = CALCULATE(COUNT(Sales[Product SKU]),DATESYTD(‘Date'[Date]))
Syntactic sugar has an aggregate value in its name, such as TOTALYTD or CLOSINGBALANCEMONTH. We will get the same results as the CALCULATE measure using the following:
Total Products Ordered YTD = TOTALYTD(COUNT(Sales[Product SKU]),’Date'[Date])
It may take generating some error messages before you get a feel for which type of function you are trying to use.