Table Functions in DAX: FILTER and ALL
What are table functions?
Table functions are functions in DAX that return a table. These are virtual tables inside of Power BI that are created using DAX functions, rather than tables that are physically within the data model. This blog post will explore two of the most common table functions –
FILTER table function is useful if you want to filter a table. The
FILTER function returns a sub-set of a table. To use the
FILTER function, you first specify a table name, followed by a condition. The condition is evaluated row by row on top the specified table and only the rows satisfying the condition will be returned as a result. So, the
FILTER function acts as both a table function and an iterator.
In order to illustrate how the
FILTER function works, we will create a new calculated table that is filtered to only return products from the 'Product' table with a colour of red.
Red Products = FILTER ('Product', 'Product'[Colour] = “Red”)
FILTER function will evaluate every single row of the 'Product' table. For each row, if the 'Colour' column has a value of 'Red', this row will be included in the new calculated table as shown below.
Whilst this example illustrates how the
FITLER function operates, in practice, you typically want to use a table function to create a temporary table that is scanned and iterated by some other function. In other words,
FILTER is useful when you want to return the result of a calculation which is done over a subset of values from a table. For example, we can create a measure that uses
FILTER to show only the sales of products which are red.
Red Sales = SUMX ( FILTER ( Sales, RELATED ( 'Product'[Colour] ) = "Red" ), Sales[Quantity] * Sales[Net Price] )
FILTER is working on the 'Sales' table, but the 'Sales' table does not contain the product colour, so we want to use the function
RELATED gives you access to columns from other tables that have a relationship with the current table you are scanning.
For each sale in the 'Sales' table, the
RELATED function looks up the 'Colour' value in the 'Product' table. The result of this lookup is used by the
FILTER function to identify and return rows where the 'Colour' column has a value of 'Red'. This temporary table is then scanned and iterated by
SUMX to calculate the
Sales[Quantity] * Sales[Price] for each row in the table. This computation calculates the sales amount for only red products.
Red Sales measure has been added to the table below to create a new column which shows the red sales for different brands.
Now, we shall explore the next table function in this blog post -
ALL returns all the rows in a table, or all the values in a column, ignoring any filters that may have been applied.
In the table below, we have the colour of each product and we have the sales amount which has been calculated via the following measure:
Sales Amount = Sales[Quantity] * Sales[Net Price]
ALL is useful if you want to compute percentages and ratios. If I want to see the percentage of red sales against all of the product sales I will need to compute the denominator of this ratio – the grand total of all the sales. We can do this using a new measure:
All Sales Amount = SUMX ( ALL ( Sales ), Sales[Quantity] * Sales[Net Price])
SUMX requires the result of
ALL ignores any filter that is applied in the report and returns all of the sales.
SUMX iterates over the whole table computing Quantity multiplied by Net Price, even if there are filters in place (such as the filter of 'Colour' in the example below). Whenever you use
ALL, any filter gets removed and you always compute the same number for all of the cells, as we can see in the table below.
Now we have the denominator
All Sales Amount, we can remove this measure from our table and create our percentage sales amount calculation:
Sales % = [Sales Amount] / [All Sales Amount]
Sales % measure can be added into the table as a new column which displays the percentage of sales of each colour over the grand total of sales.
In this blog post we have seen how the
FILTER table function can be used to filter a table and is useful when you want to return the result of a calculation over a subset of values. We have also seen how the
ALL table function can be used to return all rows of a table or values of a column, ignoring any filters applied and is useful when computing percentages and ratios.