BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.
Posted by Andy Brown on 12 January 2016
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Using the DAX FILTER function to filter data
This blog shows how you can filter tables to aggregate only over subsets of data.
Using filtered data as a replacement for a full table
Many DAX expressions ask for a table name:
A typical DAX function, asking for a table as one of its arguments.
Wherever this happens, you can supply a filtered subset of the full table's data instead, using the FILTER function.
In many cases you can use the CALCULATE function instead of the FILTER function to produce the same results (the resulting formula is usually easier to understand, too).
Our worked example: dividing transactions into cheap and expensive
Suppose we want to divide sales up as follows:
We'll create 3 measures, showing cheap, expensive and total sales. A cheap sale is one where the price was £10 or less; expensive implies a price of more than £10.
Note that you could solve this problem by creating a new aggregator column:
One way of solving the problem - but it wouldn't teach you anything about the FILTER function!
Syntax of the FILTER function
Here's how to write a FILTER function in DAX:
Our three measures could therefore be:
| Measure | Formula |
|---|---|
| Cheap | =SUMX( FILTER('Transaction',[Price]<=10),[Quantity]) |
| Expensive | =SUMX( FILTER('Transaction',[Price]>10),[Quantity]) |
| Total | =SUM('Transaction'[Quantity]) |
So for example the Cheap measure will sum the quantity, but only where the price for a transaction is less than or equal to 10 pounds.
Explanation of how this works
To see how the formula above works, consider this shaded cell, giving the total quantity of birds sold for cheap items in the West quadrant for Factory Outlet stores:
The number 6 in the shaded cell is calculated as below.
If you double-click on the total sales for Bird items for the given centre type and quadrant ...
If you double-click on the selected cell containing the number 10 ...
... you get this set of rows underlying it:
The first six cells (shown selected above) give sales of cheap items where the price is less than or equal to £10. The sum of the quantities is 6, which explains where our figure in the pivot table comes from (the equivalent total quantity for expensive items is 4).
You could have achieved exactly the same effect with the formula =CALCULATE( SUM( [Quantity]),'Transaction'[Price]<=10 ). This is IMHO much easier to understand!
Removing filters altogether
Instead of using the FILTER function to reduce the underlying set of rows for any query context, you can also use the ALL function to remove any constraints. Here's an example:
We'll calculate the percentage of total sales for each query context.
You could do this using CALCULATE by removing each dimension constraint individually, but the FILTER function allows you to remove all constraints in one go.
Here's the formula that you could use for this statistic:
Percent of total:=SUM([Quantity]) /
SUMX(
ALL( 'Transaction'),
[Quantity]
)
This will divide the total quantity of sales for the current pivot table cell by the total quantity of sales for all transactions. For tuple aficionados:
The ALL function removes all constraints from the denominator in our ratio.
Removing and then reinstating filters
Suppose that you want to show total sales as a percentage of the total sales for mammals:
We want to show total sales for each cell as a percentage of total sales for mammals. The only cell which should yield 100% is the one shown shaded.
Here's a formula which will produce these results:
Percent of total:=SUM([Quantity]) /
SUMX(
FILTER(
ALL( 'Transaction'),
[Species] = "Mammal"
),
[Quantity]
)
So the denominator of the ratio calculates the total quantity for all transactions (jettisoning any query context constraints), but with an additional requirement that the species should be Mammal.