1. Using different filters with DAX
Let’s show with an example how count and filtering functions can be used to answer questions and to improve dashboard functionality.
This dashboard contains some visuals, slicers, and a Total Amount summary card, which is currently calculated by taking the SUM() over all amounts in the fact table.
As a result, the summary card changes when a slicer or visual is selected. This is the desired result for the visuals, but not for the Total Amount summary card.
To fix this, we can edit the aggregating SUM() function to filter for all the values, regardless of which slicers or visuals are selected. You can do this by wrapping the SUM() function in a CALCULATE() function, followed by filtering all values from the fact table.
Now, the Total Amount card always shows the same result, just as you should expect from a summary card in this case.
Next, let’s have a look at the different accounts and their associated number of product categories. Each account represents some sort of expense or income, belonging to a certain amount in the fact table. To calculate the Product Category Count, you can use the DISTINCTCOUNT() function.
This seems like a strange result, having precisely eight product categories for each account. This is just the number of unique product categories present in the Product Category dimension table. The Model view reveals why this happens: we want to filter from the Dim_Account table across the Dim_ProductCategory table, but this last dimension table only filters from the dimension table into the fact table using this single relationship. In other words, there is no way of filtering the Dim_ProductCategory table, and thus counting the correct number of product categories per account, because that info needs to come from the fact table.
We could manually change the relationship to both to fix this, and now the report shows the correct number of product categories per account.
However, for performance reasons, it is more efficient to leave the relationship to single in the data view, and edit the calculation to use a CROSSFILTER() function instead.
This is achieved by wrapping the DISTINCTCOUNT() function in a CALCULATE() function, combined with a CROSSFILTER() function where you set the relationship between the Product Category dimension table and the fact table to “BOTH”.
In this way, we get the same result as before, but it becomes much more transparent which measures make use of the double relationship, and, especially for large tables, this may improve performance. Time to practice!
2. Let's practice!