Power BI: DAX: Filter Functions
The filter and value functions in Data Analysis Expressions (DAX) are some of the most complex and powerful, and differ greatly from Excel functions. The lookup functions work by using tables and relationships, like a database. The filtering functions let you manipulate data context to create dynamic calculations.
Read more about the Filter Functions in this article:
- ALL
- ALLEXCEPT
- ALLNOBLANKROW
- CALCULATE
- CALCULATETABLE
- DISTINCT
- EARLIER
- EARLIEST
- FILTER
- RELATED
- RELATEDTABLE
- VALUES
Original article (before wiki edits) was written by Jeannine Takaki and JuanPablo Jofre and formatted by Mary Browning, Microsoft SQL Server Technical Writers.
ALL
Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table. This function is not used by itself, but it serves as an intermediate function that can be used to change the set of results over which some other calculation is performed.
As described below, you can use the ALL and ALLEXCEPT functions in different scenarios.
ALL(Table) Removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied. This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value. The first example demonstrates this scenario.
ALL (Column[, Column[, …]]) Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters. The second and third examples demonstrate this scenario.
ALLEXCEPT(Table, Column1 [,Column2]...) Removes all context filters in the table except filters that are applied to the specified columns. This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table.
Syntax
ALL( {<table> | <column>[, <column>[, <column>[,…]]]} )
Table. The table that you want to clear filters on.
Column. The column that you want to clear filters on.
The argument to the ALL function must be either a reference to a base table or a reference to a base column. You cannot use table expressions or column expressions with the ALL function.
Returns a table or column with filters removed.
Example: Calculate Ratio of Category Sales to Total Sales
Assume that you want to find the amount of sales for the current cell, in your PivotTable, divided by the total sales for all resellers. To ensure that the denominator is the same regardless of how the PivotTable user might be filtering or grouping the data, you define a formula that uses ALL to create the correct grand total.
The following table shows the results when a new measure, All Reseller Sales Ratio, is created by using the formula shown below the table. To see how this works, add the field, CalendarYear, to the Row Labels area of the PivotTable, and add the field, ProductCategoryName, to the Column Labels area. Then, drag the measure, All Reseller Sales Ratio, to the Values area of the Pivot Table. To view the results as percentages, use the formatting features of Excel to apply a percentage number formatting to the cells that contains the measure.
| All Reseller Sales | Column Labels | ||||
| Row Labels | Accessories | Bikes | Clothing | Components | Grand Total |
| 2001 | 0.02% | 9.10% | 0.04% | 0.75% | 9.91% |
| 2002 | 0.11% | 24.71% | 0.60% | 4.48% | 29.90% |
| 2003 | 0.36% | 31.71% | 1.07% | 6.79% | 39.93% |
| 2004 | 0.20% | 16.95% | 0.48% | 2.63% | 20.26% |
| Grand Total | 0.70% | 82.47% | 2.18% | 14.65% | 100.00% |
Formula: =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])
The formula is constructed as follows:
- The numerator,
SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), is the sum of the values in ResellerSales_USD[SalesAmount_USD] for the current cell in the PivotTable, with context filters applied on CalendarYear and ProductCategoryName. - For the denominator, you start by specifying a table, ResellerSales_USD, and use the ALL function to remove all context filters on the table.
- You then use the SUMX function to sum the values in the ResellerSales_USD[SalesAmount_USD] column. In other words, you get the sum of ResellerSales_USD[SalesAmount_USD] for all resellers sales.
Example: Calculate Ratio of Product Sales to Total Sales Through Current Year
Assume that you want to create a table showing the percentage of sales compared over the years for each product category (ProductCategoryName). To obtain the percentage for each year over each value of ProductCategoryName, you need to divide the sum of sales for that particular year and product category by the sum of sales for the same product category over all years. In other words, you want to keep the filter on ProductCategoryName but remove the filter on the year when calculating the denominator of the percentage.
The following table shows the results when a new measure, Reseller Sales Year, is created by using the formula shown below the table. To see how this works, add the field, CalendarYear, to the Row Labels area of the PivotTable, and add the field, ProductCategoryName, to the Column Labels area. To view the results as percentages, use Excel's formatting features to apply a percentage number format to the cells containing the measure, Reseller Sales Year.
| Reseller Sales Year | |||||
| Row Labels | Accessories | Bikes | Clothing | Components | Grand Total |
| 2001 | 3.48% | 11.03% | 1.91% | 5.12% | 9.91% |
| 2002 | 16.21% | 29.96% | 27.29% | 30.59% | 29.90% |
| 2003 | 51.62% | 38.45% | 48.86% | 46.36% | 39.93% |
| 2004 | 28.69% | 20.56% | 21.95% | 17.92% | 20.26% |
| Grand Total | 100.00% | 100.00% | 100.00% | 100.00% | 100.00% |
Formula: =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/CALCULATE( SUM( ResellerSales_USD[SalesAmount_USD]), ALL(DateTime[CalendarYear]))
The formula is constructed as follows:
- The numerator,
SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), is the sum of the values in ResellerSales_USD[SalesAmount_USD] for the current cell in the pivot table, with context filters applied on the columns CalendarYear and ProductCategoryName. - For the denominator, you remove the existing filter on CalendarYear by using the ALL(Column) function. This calculates the sum over the remaining rows on the ResellerSales_USD table, after applying the existing context filters from the column labels. The net effect is that for the denominator the sum is calculated over the selected ProductCategoryName (the implied context filter) and for all values in Year.
Example: Calculate Contribution of Product Categories to Total Sales Per Year
Assume that you want to create a table that shows the percentage of sales for each product category, on a year-by-year basis. To obtain the percentage for each product category in a particular year, you need to calculate the sum of sales for that particular product category (ProductCategoryName) in year n, and then divide the resulting value by the sum of sales for the year n over all product categories. In other words, you want to keep the filter on year but remove the filter on ProductCategoryName when calculating the denominator of the percentage.
The following table shows the results when a new measure, Reseller Sales CategoryName, is created by using the formula below the table. To see how this works, add the field, CalendarYear to the Row Labels area of the PivotTable, and add the field, ProductCategoryName, to the Column Labels area. Then add the new measure to the Values area of the PivotTable. To view the results as percentages, use Excel's formatting features to apply a percentage number format to the cells that contain the new measure, Reseller Sales CategoryName.
| Reseller Sales CategoryName | |||||
| Row Labels | Accessories | Bikes | Clothing | Components | Grand Total |
| 2001 | 0.25% | 91.76% | 0.42% | 7.57% | 100.00% |
| 2002 | 0.38% | 82.64% | 1.99% | 14.99% | 100.00% |
| 2003 | 0.90% | 79.42% | 2.67% | 17.01% | 100.00% |
| 2004 | 0.99% | 83.69% | 2.37% | 12.96% | 100.00% |
| Grand Total | 0.70% | 82.47% | 2.18% | 14.65% | 100.00% |
Formula: =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/CALCULATE( SUM( ResellerSales_USD[SalesAmount_USD]), ALL(ProductCategory[ProductCategoryName]))
The formula is constructed as follows:
- The numerator,
SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), is the sum of the values in ResellerSales_USD[SalesAmount_USD] for the current cell in the PivotTable, with context filters applied on the fields, CalendarYear and ProductCategoryName. - For the denominator, you use the function, ALL(Column), to remove the filter on ProductCategoryName and calculate the sum over the remaining rows on the ResellerSales_USD table, after applying the existing context filters from the row labels. The net effect is that, for the denominator, the sum is calculated over the selected Year (the implied context filter) and for all values of ProductCategoryName.
ALLEXCEPT
Removes all context filters in the table except filters that have been applied to the specified columns. This function is not used by itself, but serves as an intermediate function that can be used to change the set of results over which some other calculation is performed.
As described below, you can use the ALL and ALLEXCEPT functions in different scenarios.
ALL(Table) Removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied. This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value.
ALL (Column[, Column[, …]]) Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters.
ALLEXCEPT(Table, Column1 [,Column2]...) Removes all context filters in the table except filters that are applied to the specified columns. This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table.
Syntax
ALLEXCEPT(<table>,<column>[,<column>[,…]])
Table. The table over which all context filters are removed, except filters on those columns that are specified in subsequent arguments.
Column. The column for which context filters must be preserved.
The first argument to the ALLEXCEPT function must be a reference to a base table; all subsequent arguments must be references to base columns. You cannot use table expressions or column expressions with the ALLEXCEPT function.
Returns a table with all filters removed except for the filters on the specified columns.
Example
This example presents a formula that you can use in a measure. The formula sums SalesAmount_USD and uses the ALLEXCEPT function to remove any context filters on the DateTime table except if the filter has been applied to the CalendarYear column.
Formula: =CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), ALLEXCEPT(DateTime, DateTime[CalendarYear]))
Because the formula uses ALLEXCEPT, whenever any column but CalendarYear from the table DateTime is used to slice the PivotTable, the formula will remove any slicer filters, providing a value equal to the sum of SalesAmount_USD for the column label value, as shown in Table 1.
However, if the column CalendarYear is used to slice the PivotTable, the results are different. Because CalendarYear is specified as the argument to ALLEXCEPT, when the data is sliced on the year, a filter will be applied on years at the row level, as shown in Table 2. You are encouraged to compare these tables to understand the behavior of ALLEXCEPT.
Table 1: Results with no filters
Table 1 shows the results when a new measure, All Sales Yearly, is created using the example formula. To see how this works, add the field, CalendarQuarter, to the Row Labels area of the PivotTable, and add the field, ProductCategoryName , to the Column Labels area. Then, add the new measure, All Sales Yearly, to the Values area of the PivotTable.
| All Sales Yearly | |||||
| Row Labels | Accessories | Bikes | Clothing | Components | Grand Total |
| 1 | $534,301.99 |
$63,084,675.05 |
$1,669,943.26 |
$11,205,837.96 |
$76,494,758.25 |
| 2 | $534,301.99 |
$63,084,675.05 |
$1,669,943.26 |
$11,205,837.96 |
$76,494,758.25 |
| 3 | $534,301.99 |
$63,084,675.05 |
$1,669,943.26 |
$11,205,837.96 |
$76,494,758.25 |
| 4 | $534,301.99 |
$63,084,675.05 |
$1,669,943.26 |
$11,205,837.96 |
$76,494,758.25 |
| Grand Total | $534,301.99 |
$63,084,675.05 |
$1,669,943.26 |
$11,205,837.96 |
$76,494,758.25 |
Note: The currency format is obtained by applying currency number formatting to All Sales Yearly.
Table 2: Results with filter preserved for calendar year
Table 2 shows how the results differ when the measure, All Sales Yearly, is used in the PivotTable, but CalendarQuarter is replaced by CalendarYear in the Row Labels. The context filters on CalendarYear that are created by the PivotTable are preserved in the results.
| All Sales Yearly | |||||
| Row Labels | Accessories | Bikes | Clothing | Components | Grand Total |
| 1 | $18,594.48 |
$6,958,251.04 |
$31,851.16 |
$574,256.99 |
$7,582,953.67 |
| 2 | $86,612.75 |
$18,901,351.08 |
$455,730.97 |
$3,428,213.05 |
$22,871,907.85 |
| 3 | $275,794.84 |
$24,256,817.51 |
$815,853.29 |
$5,195,315.22 |
$30,543,780.85 |
| 4 | $153,299.92 |
$12,968,255.41 |
$366,507.84 |
$2,008,052.70 |
$15,496,115.88 |
| Grand Total | $534,301.99 |
$63,084,675.05 |
$1,669,943.26 |
$11,205,837.96 |
$76,494,758.25 |
These results demonstrate how you can create totals but selectively keep filters on a column. You use ALLEXCEPT to remove filters in general, and add back in the columns that you want to keep as filters by using the column names as arguments to the ALLEXCEPT function.
ALLNOBLANKROW
From the parent table of a relationship, returns all rows but the blank row, or all distinct values of a column but the blank row, and disregards any context filters that might exist. The ALLNOBLANKROW function only filters the blank row that a parent table, in a relationship, will show when there are one or more rows in the child table that have non-matching values to the parent column. See the example below for a thorough explanation.
As described below, you can use the variations of ALL in different scenarios.
ALL(Column) Removes all filters from the specified column in the table; all other filters in the table, over other columns, still apply.
ALL (Table) Removes all filters from the specified table.
ALLEXCEPT(Table, Column1 [,Column2]...) Overrides all context filters in the table except over the specified columns.
ALLNOBLANKROW(table|column) From the parent table of a relationship, returns all rows but the blank row, or all distinct values of a column but the blank row, and disregards any context filters that might exist.
Syntax
ALLNOBLANKROW(<table>|<column>)
Table. The table over which all context filters are removed.
Column. The column over which all context filters are removed.
Only one parameter must be passed; the parameter is either a table or a column.
Returns a table when the passed parameter was a table; returns a column of values when the passed parameter was a column.
Example
In the sample data, the ResellerSales_USD table contains one row that has no values and therefore cannot be related to any of the parent tables in the relationships within the workbook. You will use this table in a PivotTable so that you can see the blank row behavior and how to handle counts on unrelated data.
Step 1: Verify the unrelated data
Open the PowerPivot window, then select the ResellerSales_USD table. In the ProductKey column, filter for blank values. One row will remain. In that row, all column values should be blank except for SalesOrderLineNumber.
Step 2: Create a PivotTable
Create a new PivotTable, then drag the column, datetime.[Calendar Year], to the Row Labels pane. The following table shows the expected results:
| Row Labels |
| 2001 |
| 2002 |
| 2003 |
| 2004 |
| Grand Total |
Note the blank label between 2004 and Grand Total. This blank label represents the Unknown member, which is a special group created by PowerPivot to account for any values in the child table that have no matching value in the parent table, in this example the datetime.[Calendar Year] column.
When you see this blank label in the PivotTable, you know that in some of the tables that are related to the column, datetime.[Calendar Year], there are either blank values or non-matching values. The parent table is the one that shows the blank label, but the rows that do not match are in one or more of the child tables.
The rows that get added to this blank label group are either values that do not match any value in the parent table-- for example, a date that does not exist in the datetime table-- or null values, meaning no value for date at all. In this example we have placed a blank value in all columns of the child sales table. Having more values in the parent table than in the children tables does not cause a problem.
Step 3: Count rows using ALL and ALLNONBLANK
Add the following two measures to the datetime table, to count the table rows: Countrows ALLNOBLANK of datetime, Countrows ALL of datetime. The formulas that you can use to define these measures are given in the code section following.
On a blank PivotTable add datetime.[Calendar Year] column to the row labels, and then add the newly created measures. The results should look like the following table:
| Row Labels | Countrows ALLNOBLANK of datetime | Countrows ALL of datetime |
| 2001 | 1280 |
1281 |
| 2002 | 1280 |
1281 |
| 2003 | 1280 |
1281 |
| 2004 | 1280 |
1281 |
1280 |
1281 |
|
| Grand Total | 1280 |
1281 |
The results show a difference of 1 row in the table rows count. However, if you open the PowerPivot window and select the datetime table, you cannot find any blank row in the table because the special blank row mentioned here is the Unknown member.
Step 4: Verify that the count is accurate
In order to prove that the ALLNOBLANKROW does not count any truly blank rows, and only handles the special blank row on the parent table only, add the following two measures to the ResellerSales_USD table: Countrows ALLNOBLANKROW of ResellerSales_USD, Countrows ALL of ResellerSales_USD.
Create a new PivotTable, and drag the column, datetime.[Calendar Year], to the Row Labels pane. Now add the measures that you just created. The results should look like the following:
| Row Labels | Countrows ALLNOBLANKROW of ResellerSales_USD |
Countrows ALL of ResellerSales_USD |
| 2001 | 60856 |
60856 |
| 2002 | 60856 |
60856 |
| 2003 | 60856 |
60856 |
| 2004 | 60856 |
60856 |
60856 |
60856 |
|
| Grand Total | 60856 |
60856 |
Now the two measures have the same results. That is because the ALLNOBLANKROW function does not count truly blank rows in a table, but only handles the blank row that is a special case generated in a parent table, when one or more of the child tables in the relationship contain non-matching values or blank values.
// Countrows ALLNOBLANK of datetime
= COUNTROWS(ALLNOBLANKROW('DateTime'))
// Countrows ALL of datetime
= COUNTROWS(ALL('DateTime'))
// Countrows ALLNOBLANKROW of ResellerSales_USD
=COUNTROWS(ALLNOBLANKROW('ResellerSales_USD'))
// Countrows ALL of ResellerSales_USD
=COUNTROWS(ALL('ResellerSales_USD'))
CALCULATE
Evaluates an expression in a context that is modified by the specified filters. If the data has been filtered, the CALCULATE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. For each column used in a filter argument, any existing filters on that column are removed, and the filter used in the filter argument is applied instead.
Whereas the CALCULATE function requires as its first argument an expression that returns a single value, the CALCULATETABLE function takes a table of values.
Syntax
CALCULATE(<expression>,<filter1>,<filter2>…)
Expression. The expression to be evaluated.
Filter1, filter2, . . . (optional) A comma-separated list of Boolean expression or a table expression that defines a filter.
The expression used as the first parameter is essentially the same as a measure.
The following restrictions apply to Boolean expressions that are used as arguments:
- The expression cannot reference a measure.
- The expression cannot use a nested CALCULATE function.
- The expression cannot use any function that scans a table or returns a table, including aggregation functions.
However, a Boolean expression can use any function that looks up a single value, or that calculates a scalar value.
Returns the value that is the result of the expression.
Example
To calculate the ratio of current reseller sales to all reseller sales, you add to the PivotTable a measure that calculates the sum of sales for the current cell (the numerator), and then divides that sum by the total sales for all resellers (the denominator). To ensure that the denominator remains the same regardless of how the PivotTable might be filtering or grouping the data, the part of the formula that represents the denominator must use the ALL function to clear any filters and create the correct total.
The following table shows the results when the new measure, named All Reseller Sales Ratio, is created by using the formula shown below the table.
To see how this works, add the field, CalendarYear, to the Row Labels area of the PivotTable, and add the field, ProductCategoryName, to the Column Labels area. Then add the new measure to the Values area of the PivotTable. To display the numbers as percentages, apply percentage number formatting to the area of the PivotTable that contains the new measure, All Reseller Sales Ratio.
| All Reseller Sales | |||||
| Row Labels | Accessories | Bikes | Clothing | Components | Grand Total |
| 2001 | 0.02% | 9.10% | 0.04% | 0.75% | 9.91% |
| 2002 | 0.11% | 24.71% | 0.60% | 4.48% | 29.90% |
| 2003 | 0.36% | 31.71% | 1.07% | 6.79% | 39.93% |
| 2004 | 0.20% | 16.95% | 0.48% | 2.63% | 20.26% |
| Grand Total | 0.70% | 82.47% | 2.18% | 14.65% | 100.00% |
Formula: =( SUM('ResellerSales_USD'[SalesAmount_USD]))
/CALCULATE( SUM('ResellerSales_USD'[SalesAmount_USD])
,ALL('ResellerSales_USD'))
CALCULATETABLE
Evaluates a table expression in a context modified by the given filters. The CALCULATETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. For each column used in a filter argument, any existing filters on that column are removed, and the filter used in the filter argument is applied instead.
This function is a synonym for the RELATEDTABLE function.
Syntax
CALCULATETABLE(<expression>,<filter1>,<filter2>…)
Expression. The table expression to be evaluated.
Filter1, filter2, . . . (optional) A Boolean expression or a table expression that defines a filter.
The expression used as the first parameter must be a function that returns a table.
The following restrictions apply to Boolean expressions that are used as arguments:
- The expression cannot reference a measure.
- The expression cannot use a nested CALCULATE function.
- The expression cannot use any function that scans a table or returns a table, including aggregation functions.
However, a Boolean expression can use any function that looks up a single value, or that calculates a scalar value.
Returns a table of values.
Example
This example uses the CALCULATETABLE function to get the sum of Internet sales for 2002. This value is later used to calculate the ratio of Internet sales compared to all sales for the year 2002.
The following table shows the results from the formula below the table.
| Row Labels | Internet SalesAmount_USD |
CalculateTable 2002 Internet Sales |
Internet Sales to 2002 ratio |
| 2001 | $2,627,031.40 |
$5,681,440.58 |
0.46 |
| 2002 | $5,681,440.58 |
$5,681,440.58 |
1.00 |
| 2003 | $8,705,066.67 |
$5,681,440.58 |
1.53 |
| 2004 | $9,041,288.80 |
$5,681,440.58 |
1.59 |
| Grand Total | $26,054,827.45 |
$5,681,440.58 |
4.59 |
Formula: =SUMX( CALCULATETABLE('InternetSales_USD', 'DateTime'[CalendarYear]=2002)
, [SalesAmount_USD])
DISTINCT
Returns a one-column table that contains the distinct values from the specified column. In other words, duplicate values are removed and only unique values are returned.
Note: This function cannot be used to return values into a cell or column on a worksheet; rather, you nest the DISTINCT function within a formula, to get a list of distinct values that can be passed to another function and then counted, summed, or used for other operations.
The results of DISTINCT are affected by the current filter context. For example, if you use the formula in the following example to create a measure, the results would change whenever the table was filtered to show only a particular region or a time period. If you want to prevent filtering from affecting the items in the list, use the ALL function to remove filters from the specified column and table, like this:
=COUNTROWS(DISTINCT(ALL( InternetSales_USD[CustomerKey])))
The VALUES function is similar to DISTINCT; it can also be used to return a list of unique values, and generally will return exactly the same results as DISTINCT. However, in some context VALUES will return one additional special value.
Syntax
DISTINCT(<column>)
Column. The column from which unique values are to be returned. Or, an expression that returns a column.
Example
The following formula counts the number of unique customers who have generated orders over the internet channel. The table that follows illustrates the possible results when the formula is added to a PivotTable.
=COUNTROWS(DISTINCT(InternetSales_USD[CustomerKey]))
Note that you cannot paste the list of values that DISTINCT returns directly into a column. Instead, you pass the results of the DISTINCT function to another function that counts, filters, or aggregates values by using the list. To make the example as simple as possible, here the table of distinct values has been passed to the COUNTROWS function.
| Unique Internet Customers | ||||
| Row Labels | Accessories | Bikes | Clothing | Grand Total |
| 2001 | 1013 | 1013 | ||
| 2002 | 2677 | 2677 | ||
| 2003 | 6792 |
4875 |
2867 |
9309 |
| 2004 | 9435 |
5451 |
4196 |
11377 |
| Grand Total | 15114 |
9132 |
6852 |
18484 |
Also, note that the results are not additive. That is to say, the total number of unique customers in 2003 is not the sum of unique customers of Accessories, Bikes and Clothing for that year. The reason is that a customer can be counted in multiple groups.
EARLIER
Returns the current value of the specified column in an outer evaluation pass of the mentioned column.
EARLIER is useful for nested calculations where you want to use a certain value as an input and produce calculations based on that input. In Microsoft Excel, you can do such calculations only within the context of the current row; however, in PowerPivot you can store the value of the input and then make calculation using data from the entire table.
EARLIER is mostly used in the context of calculated columns.
EARLIER succeeds if there is a row context prior to the beginning of the table scan. Otherwise it returns an error.
The performance of EARLIER might be slow because it theoretically, it might have to perform a number of operations that is close to the total number of rows (in the column) times the same number (depending on the syntax of the expression). For example if you have 10 rows in the column, approximately a 100 operations could be required; if you have 100 rows then close to 10,000 operations might be performed.
Note: In practice, the VertiPaq engine performs optimizations to reduce the actual number of calculations, but you should be cautious when creating formulas that involve recursion.
Returns the current value of row, from column, at number of outer evaluation passes (where column and number are defined below).
Syntax
EARLIER(<column>, <number>)
Column. A column or expression that resolves to a column.
Number. (Optional) A positive number to the outer evaluation pass.
The next evaluation level out is represented by 1; two levels out is represented by 2, and so on.
When omitted, the default value is 1.
Example
To illustrate the use of EARLIER, it is necessary to build a scenario that calculates a rank value and then uses that rank value in other calculations.
The following example is based on this simple table, ProductSubcategory, which shows the total sales for each ProductSubcategory.
The final table, including the ranking column is shown here.
ProductSubcategoryKey |
EnglishProductSubcategoryName |
TotalSubcategorySales |
SubcategoryRanking |
| 18 | Bib-Shorts | $156,167.88 |
18 |
| 26 | Bike Racks | $220,720.70 |
14 |
| 27 | Bike Stands | $35,628.69 |
30 |
| 28 | Bottles and Cages | $59,342.43 |
24 |
| 5 | Bottom Brackets | $48,643.47 |
27 |
| 6 | Brakes | $62,113.16 |
23 |
| 19 | Caps | $47,934.54 |
28 |
| 7 | Chains | $8,847.08 |
35 |
| 29 | Cleaners | $16,882.62 |
32 |
| 8 | Cranksets | $191,522.09 |
15 |
| 9 | Derailleurs | $64,965.33 |
22 |
| 30 | Fenders | $41,974.10 |
29 |
| 10 | Forks | $74,727.66 |
21 |
| 20 | Gloves | $228,353.58 |
12 |
| 4 | Handlebars | $163,257.06 |
17 |
| 11 | Headsets | $57,659.99 |
25 |
| 31 | Helmets | $451,192.31 |
9 |
| 32 | Hydration Packs | $96,893.78 |
20 |
| 21 | Jerseys | $699,429.78 |
7 |
| 33 | Lights | 36 | |
| 34 | Locks | $15,059.47 |
33 |
| 1 | Mountain Bikes | $34,305,864.29 |
2 |
| 12 | Mountain Frames | $4,511,170.68 |
4 |
| 35 | Panniers | 36 | |
| 13 | Pedals | $140,422.20 |
19 |
| 36 | Pumps | $12,695.18 |
34 |
| 2 | Road Bikes | $40,551,696.34 |
1 |
| 14 | Road Frames | $3,636,398.71 |
5 |
| 15 | Saddles | $52,526.47 |
26 |
| 22 | Shorts | $385,707.80 |
10 |
| 23 | Socks | $28,337.85 |
31 |
| 24 | Tights | $189,179.37 |
16 |
| 37 | Tires and Tubes | $224,832.81 |
13 |
| 3 | Touring Bikes | $13,334,864.18 |
3 |
| 16 | Touring Frames | $1,545,344.02 |
6 |
| 25 | Vests | $240,990.04 |
11 |
| 17 | Wheels | $648,240.04 |
8 |
Creating a Rank Value
One way to obtain a rank value for a given value in a row is to count the number of rows, in the same table, that have a value larger (or smaller) than the one that is being compared. This technique returns a blank or zero value for the highest value in the table, whereas equal values will have the same rank value and next value (after the equal values) will have a non consecutive rank value. See the sample below.
A new calculated column, SubCategorySalesRanking, is created by using the following formula.
= COUNTROWS(FILTER(ProductSubcategory, EARLIER(ProductSubcategory[TotalSubcategorySales])<ProductSubcategory[TotalSubcategorySales]))+1
The following steps describe the method of calculation in more detail.
- The EARLIER function gets the value of TotalSubcategorySales for the current row in the table. In this case, because the process is starting, it is the first row in the table
- EARLIER([TotalSubcategorySales]) evaluates to $156,167.88, the current row in the outer loop.
- The FILTER function now returns a table where all rows have a value of TotalSubcategorySales larger than $156,167.88 (which is the current value for EARLIER).
- The COUNTROWS function counts the rows of the filtered table and assigns that value to the new calculated column in the current row plus 1. Adding 1 is needed to prevent the top ranked value from become a Blank.
- The calculated column formula moves to the next row and repeats steps 1 to 4. These steps are repeated until the end of the table is reached.
The EARLIER function will always get the value of the column prior to the current table operation. If you need to get a value from the loop before that, set the second argument to 2.
EARLIEST
Returns the current value of the specified column in an outer evaluation pass of the specified column.
The EARLIEST function is similar to EARLIER, but lets you specify one additional level of recursion.
Returns a column with filters removed.
Syntax
EARLIEST(<column>)
Column. A reference to a column.
Example
No examples are currently available. Add one here.
FILTER
Returns a table that represents a subset of another table or expression.
You can use FILTER to reduce the number of rows in the table that you are working with, and use only specific data in calculations. FILTER is not used independently, but as a function that is embedded in other functions that require a table as an argument.
Returns a table containing only the filtered rows.
Syntax
FILTER(<table>,<filter>)
Table. The table to be filtered. The table can also be an expression that results in a table.
Filter. A Boolean expression that is to be evaluated for each row of the table. For example, [Amount] > 0 or [Region] = "France"
Example
The following example creates a report of Internet sales outside the United States by using a measure that filters out sales in the United States, and then slicing by calendar year and product categories. To create this measure, you filter the table, Internet Sales USD, by using Sales Territory, and then use the filtered table in a SUMX function.
In this example, the expression FILTER('InternetSales_USD', RELATED('SalesTerritory'[SalesTerritoryCountry])<>"United States") returns a table that is a subset of Internet Sales minus all rows that belong to the United States sales territory. The RELATED function is what links the Territory key in the Internet Sales table to SalesTerritoryCountry in the SalesTerritory table.
The following table demonstrates the proof of concept for the measure, NON USA Internet Sales, the formula for which is provided in the code section below. The table compares all Internet sales with non- USA Internet sales, to show that the filter expression works, by excluding United States sales from the computation.
To re-create this table, add the field, SalesTerritoryCountry, to the Row Labels area of the PivotTable.
Table 1. Comparing total sales for U.S. vs. all other regions
| Row Labels | Internet Sales | Non USA Internet Sales |
| Australia | $4,999,021.84 |
$4,999,021.84 |
| Canada | $1,343,109.10 |
$1,343,109.10 |
| France | $2,490,944.57 |
$2,490,944.57 |
| Germany | $2,775,195.60 |
$2,775,195.60 |
| United Kingdom | $5,057,076.55 |
$5,057,076.55 |
| United States | $9,389,479.79 |
|
| Grand Total | $26,054,827.45 |
$16,665,347.67 |
The final report table shows the results when you create a PivotTable by using the measure, NON USA Internet Sales. Add the field, CalendarYear, to the Row Labels area of the PivotTable and add the field, ProductCategoryName, to the Column Labels area.
Table 2. Comparing non- U.S. sales by product categories
| Non USA Internet Sales | ||||
| Row Labels | Accessories | Bikes | Clothing | Grand Total |
| 2001 | $1,526,481.95 |
$1,526,481.95 |
||
| 2002 | $3,554,744.04 |
$3,554,744.04 |
||
| 2003 | $156,480.18 |
$5,640,106.05 |
$70,142.77 |
$5,866,729.00 |
| 2004 | $228,159.45 |
$5,386,558.19 |
$102,675.04 |
$5,717,392.68 |
| Grand Total | $384,639.63 |
$16,107,890.23 |
$172,817.81 |
$16,665,347.67 |
Formula: =SUMX(FILTER('InternetSales_USD', RELATED('SalesTerritory'[SalesTerritoryCountry])<>"United States")
,'InternetSales_USD'[SalesAmount_USD])
RELATED
Returns a related value from another table.
The RELATED function requires that a relationship exists between the current table and the table with related information. You specify the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table.
If a relationship does not exist, you must create a relationship.
When the RELATED function performs a lookup, it examines all values in the specified table regardless of any filters that may have been applied.
Note: The RELATED function needs a row context; therefore, it can only be used in a calculated column expression, where the current row context is unambiguous, or as a nested function in an expression that uses a table scanning function. A table scanning function, such as SUMX, gets the value of the current row value and then scans another table for instances of that value.
Returns a single value that is related to the current row.
Syntax
RELATED(<column>)
Column. The column that contains the values you want to retrieve.
Example
In the following example, the measure Non USA Internet Sales is created to produce a sales report that excludes sales in the United States. In order to create the measure, the InternetSales_USD table must be filtered to exclude all sales that belong to the United States in the SalesTerritory table. The United States, as a country, appears 5 times in the SalesTerritory table; once for each of the following regions: Northwest, Northeast, Central, Southwest, and Southeast.
The first approach to filter the Internet Sales, in order to create the measure, could be to add a filter expression like the following:
FILTER('InternetSales_USD', 'InternetSales_USD'[SalesTerritoryKey]<>1 && 'InternetSales_USD'[SalesTerritoryKey]<>2 && 'InternetSales_USD'[SalesTerritoryKey]<>3 && 'InternetSales_USD'[SalesTerritoryKey]<>4 && 'InternetSales_USD'[SalesTerritoryKey]<>5)
However, this approach is counterintuitive, prone to typing errors, and might not work if any of the existing regions is split in the future.
A better approach would be to use the existing relationship between InternetSales_USD and SalesTerritory and explicitly state that the country must be different from the United States. To do so, create a filter expression like the following:
FILTER( 'InternetSales_USD', RELATED('SalesTerritory'[SalesTerritoryCountry])<>"United States")
This expression uses the RELATED function to lookup the country value in the SalesTerritory table, starting with the value of the key column, SalesTerritoryKey, in the InternetSales_USD table. The result of the lookup is used by the filter function to determine if the InternetSales_USD row is filtered or not.
Note: If the example does not work, you might need to create a relationship between the tables.
Formula: = SUMX(FILTER( 'InternetSales_USD'
, RELATED('SalesTerritory'[SalesTerritoryCountry])
<>"United States"
)
,'InternetSales_USD'[SalesAmount_USD])
The following table shows only totals for each region, to prove that the filter expression in the measure, Non USA Internet Sales, works as intended.
| Row Labels | Internet Sales | Non USA Internet Sales |
| Australia | $4,999,021.84 |
$4,999,021.84 |
| Canada | $1,343,109.10 |
$1,343,109.10 |
| France | $2,490,944.57 |
$2,490,944.57 |
| Germany | $2,775,195.60 |
$2,775,195.60 |
| United Kingdom | $5,057,076.55 |
$5,057,076.55 |
| United States | $9,389,479.79 |
|
| Grand Total | $26,054,827.45 |
$16,665,347.67 |
The following table shows the final report that you might get if you used this measure in a PivotTable:
| Non USA Internet Sales | ||||
| Row Labels | Accessories | Bikes | Clothing | Grand Total |
| 2001 | $1,526,481.95 |
$1,526,481.95 |
||
| 2002 | $3,554,744.04 |
$3,554,744.04 |
||
| 2003 | $156,480.18 |
$5,640,106.05 |
$70,142.77 |
$5,866,729.00 |
| 2004 | $228,159.45 |
$5,386,558.19 |
$102,675.04 |
$5,717,392.68 |
| Grand Total | $384,639.63 |
$16,107,890.23 |
$172,817.81 |
$16,665,347.67 |
RELATEDTABLE
Evaluates a table expression in a context modified by the given filters.
The RELATEDTETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. For each column used in a filter argument, any existing filters on that column are removed, and the filter used in the filter argument is applied instead.
This function is a synonym for the CALCULATETABLE function.
Returns a table of values.
Syntax
RELATEDTABLE(<expression>,<filter1>,<filter2>,…)
Expression. The table expression to be evaluated.
Filter1, filter2, . . . A Boolean expression or a table expression that defines a filter.
The expression used as the first parameter must be a table or an expression that returns a table.
The following restrictions apply to Boolean expressions that are used as arguments:
- The expression cannot reference a measure.
- The expression cannot use a nested CALCULATE function.
- The expression cannot use any function that scans a table or returns a table, including aggregation functions.
However, a Boolean expression can use any function that looks up a single value, or that calculates a scalar value.
Example
The following example uses the RELATEDTABLE function to get Internet Sales for 2002; this value is later used to calculate a ratio of sales compared to the sales in year 2002.
The following table shows the results of using the code shown after the table.
| Row Labels | Internet SalesAmount_USD | RelatedTable 2002 Internet Sales |
Internet Sales to 2002 ratio |
| 2001 | $2,627,031.40 |
$5,681,440.58 |
0.46 |
| 2002 | $5,681,440.58 |
$5,681,440.58 |
1.00 |
| 2003 | $8,705,066.67 |
$5,681,440.58 |
1.53 |
| 2004 | $9,041,288.80 |
$5,681,440.58 |
1.59 |
| Grand Total | $26,054,827.45 |
$5,681,440.58 |
4.59 |
Formula: = SUMX( RELATEDTABLE('InternetSales_USD', 'DateTime'[CalendarYear]=2002)
, [SalesAmount_USD])
VALUES
Returns a one-column table that contains the distinct values from the specified column. In other words, duplicate values are removed and only unique values are returned.
Note: This function cannot be used to return values into a cell or column on a worksheet; rather, you use it as an intermediate function, nested in a formula, to get a list of distinct values that can be counted, or used to filter or sum other values.
When you use the VALUES function in a context that has been filtered, such as in a PivotTable, the unique values returned by VALUES are affected by the filter. For example, if you filter by Region, and return a list of the values for City, the list will include only those cities in the regions permitted by the filter. To return all of the cities, regardless of existing filters, you must use the ALL function to remove filters from the table.
Returns a column of unique values.
In most scenarios, the results of the VALUES function are identical to those of the DISTINCT function. Both functions remove duplicates and return a list of the possible values in the specified column. However, the VALUES function can also return an Unknown member. This unknown value is useful in cases where you are looking up distinct values from a related table, but a value used in the relationship is missing from one table. In database terminology, this is termed a violation of referential integrity. Such mismatches in data can easily occur when one table is being updated and the related table is not.
The following table summarizes the mismatch between data that can occur in two related tables when referential integrity is not preserved.
| MyOrders table | MySales table |
| June 1 | June 1 sales |
| June 2 | June 2 sales |
| (no order dates have been entered) | June 3 sales |
If you used the DISTINCT function to return a list of dates from the PivotTable containing these tables, only two dates would be returned. However, if you use the VALUES function, the function returns the two dates plus an additional blank member. Also, any row from the MySales table that does not have a matching date in the MyOrders table will be "matched" to this unknown member.
Syntax
VALUES(<column>)
Column. The column from which unique values are to be returned.
Example
The formula shown after the table counts the number of unique invoices (sales orders), and produces the following results when used in a report that includes the Product Category Names:
| Row Labels | Count Invoices |
| Accessories | 18,208 |
| Bikes | 15,205 |
| Clothing | 7,461 |
| Grand Total | 27,659 |
Formula: =COUNTROWS(VALUES('InternetSales_USD'[SalesOrderNumber]))
See Also
External Links