Filter Data in DAX Formulas

In this article

• Creating a Filter on a Table used in a Formula

• Filters that Remove Duplicates

• How Context Affects Filters

• Removing Filters

  • Overriding All Filters with the ALL Function

  • Overriding Specific Filters with the ALLEXCEPT Function

Creating a Filter on a Table used in a Formula

You can apply filters in formulas that take a table as input. Instead of entering a table name, you use the FILTER function to define a subset of rows from the specified table. That subset then is passed to another function, for operations such as custom aggregations.

For example, suppose you have a table of data that contains order information about resellers, and you want to calculate how much each reseller sold. However, you want to show the sales amount just for those resellers who sold multiple units of your higher-value products. The following formula, based on the DAX sample workbook, shows one example of how you can create this calculation by using a filter:

=SUMX(
     FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Quantity] > 5 &&
     'ResellerSales_USD'[ProductStandardCost_USD] > 100),
     'ResellerSales_USD'[SalesAmt]
     )

• The first part of the formula specifies one of the Power Pivot aggregation functions, which takes a table as an argument. SUMX calculates a sum over a table.

• The second part of the formula, FILTER(table, expression),tells SUMX which data to use. SUMX requires a table or an expression that results in a table. Here, instead of using all the data in a table, you use the FILTER function to specify which of the rows from the table are used.

  The filter expression has two parts: the first part names the table to which the filter applies. The second part defines an expression to use as the filter condition. In this case, you are filtering on resellers who sold more than 5 units and products that cost more than $100. The operator, &&, is a logical AND operator, which indicates that both parts of the condition must be true for the row to belong to the filtered subset.

• The third part of the formula tells the SUMX function which values should be summed. In this case you are using just the sales amount.

  Note that functions such as FILTER, which return a table, never return the table or rows directly, but are always embedded in another function. For more information about FILTER and other functions used for filtering, including more examples, see Filter Functions (DAX).

  Note: The filter expression is affected by the context in which it is used. For example, if you use a filter in a measure, and the measure is used in a PivotTable or PivotChart, the subset of data that is returned may be affected by additional filters or Slicers that the user has applied in the PivotTable. For more information about context, see Context in DAX Formulas.

Filters that Remove Duplicates

In addition to filtering for specific values, you can return a unique set of values from another table or column. This can be helpful when you want to count the number of unique values in a column, or use a list of unique values for other operations. DAX provides two functions for returning distinct values: DISTINCT Function and VALUES Function.

• The DISTINCT function examines a single column that you specify as an argument to the function, and returns a new column containing just the distinct values.

• The VALUES function also returns a list of unique values, but also returns the Unknown member. This is useful when you use values from two tables that are joined by a relationship, and a value is missing in one table and present in the other. For more information about the Unknown member, see Context in DAX Formulas.

Both of these functions return an entire column of values; therefore, you use the functions to get a list of values that is then passed to another function. For example, you could use the following formula to get a list of the distinct products sold by a particular reseller, using the unique product key, and then count the products in that list by using the COUNTROWS function:

=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))

Top of Page

How Context Affects Filters

When you add a DAX formula to a PivotTable or PivotChart, the results of the formula can be affected by the context. If you are working in a Power Pivot table, the context is the current row and its values. If you are working in a PivotTable or PivotChart, the context means the set or subset of data that is defined by operations such as slicing, or filtering. The design of the PivotTable or PivotChart also imposes its own context. For example, if you create a PivotTable that groups sales by region and year, only the data that applies to those regions and years appears in the PivotTable. Therefore any measures that you add to the PivotTable are calculated in the context of the column and row headings plus any filters in the measure formula.

For more information, see Context in DAX Formulas.

Top of Page

Removing Filters

When working with complex formulas, you might want to know exactly what the current filters are, or you might want to modify the filter part of the formula. DAX provides several functions that enable you to remove filters, and to control which columns are retained as part of the current filter context. This section provides an overview of how these functions affect results in a formula.