559 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
How to use the FILTER function in DAX to filter tables |
---|
Although the CALCULATE function is by more useful (and often easier to understand), DAX programmers should also learn about the FILTER function. This provides another way to change the query context for any aggregation.
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
This blog shows how you can filter tables to aggregate only over subsets of data.
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).
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!
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.
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!
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.
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.
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.