1. Filtering and counting with DAX
Welcome back! I'm Maarten, and the second instructor for this course. In this chapter, we'll discuss some more examples of DAX measures in Power BI. We'll start with filtering and counting functions.
2. Filter functions
In the previous chapter, you've seen that, on top of row and query context, filters are applied on the filter context. This means that filters take precedence over any slicer or visuals on your dashboard. Therefore, filter functions are great for situations where you need visuals that aren't affected by the query context. Let's show with an example. Suppose you're creating a report with lots of slicers and visuals that interact, but you always want to show the total sales value on a summary card that is not affected by any selection. The usual way of calculating the Total Sales measure would be by taking the sum.
3. Filter functions
However, a selection made with a Region slicer would impact the Total Sales value, which we want to avoid in this case. This is where the CALCULATE() function comes into play.
CALCULATE() allows you to evaluate an expression within one, two, or more filter contexts. It is often used with intermediate functions; such as the ALL() function, which ignores any filters that might have been applied.
4. Filter functions
As a result, a Region or any other slicer won't affect our calculation, which is what we want for our summary card in this case. You'll see an example in the exercises.
5. More filter options
In addition to ALL(), there are two other common filter functions that we'll discuss. The first one is FILTER(), which returns a table that represents a filtered version of your target table. Let's say for example that we want to filter the orders to show only the sales for a certain salesperson, Chuck.
6. More filter options
We can use the FILTER() function within the calculate function to filter the fact table, to only include values of the related dimensional table for the salesperson Chuck. Since the filter context always precedes the query context, the FILTER() function allows you to set a value on a visual. Note that the RELATED() function is used to return values from the Salesperson column of the Sales dimension.
7. More filter options
The final common function to discuss is CROSSFILTER(), which is a relationship function that can also be used in a filter context. CROSSFILTER() specifies the cross-filtering direction to be used in a calculation.
In this example, a single direction relationship is defined between two tables. CROSSFILTER() can override this, by setting the direction to 'both' for a particular measure you want to calculate.
8. The benefits of filtering in DAX
So, what are the main benefits of filtering in DAX? The first reason is that it can improve performance, by eliminating unnecessary data for a specific calculated measure, or by defining specific relationships between tables.
Secondly, it allows for easy reusability of measures, since you can refer to other measures in a calculated measure.
Lastly, it helps when specifying complex calculations, by using a concise syntax instead of separate measures and several lines of code.
9. Counting
Another common set of functions are the counting functions. It comes in different flavors, but here are the most used ones.
The normal COUNT() function is great for counting non-blank rows that consist of numbers, dates, and strings.
COUNTA() is similar to COUNT(), but it also counts boolean values such as True or False.
To count the number of blank rows, you can use the COUNTBLANKS() function, and if you want the number of distinct values in a column, use the DISTINCTCOUNT() function.
Finally, to return the number of rows that don't have blank values in an entire table, the COUNTROWS() function is used, which takes a table as argument.
10. Let's practice!
Let's see whether you filtered out the essentials of this lesson.