Table of contents
TOC
Collapse the table of content
Expand the table of content

DAX Semantic error: One or more expressions defined as filter arguments cannot be used in this expression. Rewrite your filter expression using the FILTER function.

Owen Duncan|Last Updated: 10/5/2018
|
1 Contributor

Cause

This error can appear when one or more filter expressions cannot be used in context of the measure or calculated column expression.

In most cases, this error is caused by a filter expression specified as an argument to the CALCULATE Function (DAX) function. The CALCULATE function requires filters defined as a Boolean expression or a table expression.

For example, the following measure expression returns the sum of SalesAmount in the Sales table for all sales in 2013. There is a relationship between the Sales table and a Date table with a CalendarYear column.

=CALCULATE(  
SUM(Sales[SalesAmount]),   
'Date'[CalendarYear] = 2013)   
)  

In this case, 'Date'[CalendarYear] = 2013 is a filter expression passed to the CALCULATE function. No error is returned and sum of SalesAmount is calculated successfully for all sales in 2013.

This next measure expression attempts to return the sum of SalesAmount for the last year in CalendarYear.

=CALCULATE(  
SUM(Sales[SalesAmount]),   
MAX(Date[CalendarYear])  
)  

The filter expression, MAX('Date'[CalendarYear]) attempts to return the largest numeric value in the CalendarYear column. However, in context of the measure expression, it cannot be passed as a table filter expression to the CALCULATE function, causing an error.

To fix this error

Use the FILTER Function (DAX) function to define filters as a table expression.

Using the example above, to return the last year in CalendarYear, the FILTER function can be used to return a table filtered by another filter expression; one that uses the MAX Function (DAX) function to return the last year:

=CALCULATE(  
SUM(Sales[SalesAmount]),  
FILTER(  
ALL(  
       'Date'[CalendarYear]),   
      [CalendarYear] = MAX('Date'[CalendarYear])  
)  
)  

The FILTER function evaluates the Date table according to the defined filter:

ALL('Date'[CalendarYear]), [CalendarYear] = MAX('Date'[CalendarYear])  

This expression uses the MAX function to evaluate the CalendarYear column in the Date table and return the largest number. The ALL Function (DAX) function is used to clear any additional filters and evaluate all rows in the Date table. The FILTER function then passes the last year as a table filter expression to the CALCULATE function. The sum of SalesAmount is then evaluated by the table filter expression containing a single row and a single column, with the last year in CalendarYear. No error is returned and the calculation completes successfully.

© 2018 Microsoft