Using CALCULATE Function DAX
You can use the CALCULATE Function DAX for evaluating a table expression in a modified filter context. Also, it modifies the filter context applied to an expression that returns a scalar value.
DAXCopy – Syntax
CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, …]]])
Parameters
Term | Definition |
---|---|
expression | The table expression to be evaluated. |
filter1, filter2,… | (Optional) Boolean expressions or table expressions that defines filters, or filter modifier functions. |
Remember, the expression used as the first parameter must be a model table or a function that returns a table.
Types of Filters
- Boolean filter expressions
- Table filter expressions
- Filter modification functions
Also, when there are multiple filters, they are evaluated by using the AND logical operator. That indicates all conditions must be TRUE at the same time.
Boolean filter expressions
A Boolean expression filter is an expression that evaluates to TRUE or FALSE. There are several rules that they must abide by:
- They can reference only a single column.
- They cannot reference measures.
- They cannot use a nested CALCULATE function.
- They cannot use functions that scan or return a table, including aggregation functions.
Table filter expression
The table expression filter implements a table object as a filter. This could be a reference to a model table, but it’s a function that returns a table object. Also, you can use the FILTER function to apply complex filter conditions, including those that cannot be defined by a Boolean filter expression.
Filter modifier functions
The filter modification functions permits you to do more than simply add filters. They provide you with additional control when modifying filter context.
Function | Objectives |
---|---|
REMOVEFILTERS | It is used to remove all filters, or filters from one or more columns of a table, or from all columns of a single table. |
ALL 1, ALLEXCEPT, ALLNOBLANKROW | It is used to remove filters from one or more columns, or from all columns of a single table. |
KEEPFILTERS | It is used to add filter without removing existing filters on the same columns. |
USERELATIONSHIP | It is used to engage an inactive relationship between related columns, in which case the active relationship will automatically become inactive. |
CROSSFILTER | It is used to modify filter direction (from both to single, or from single to both) or disable a relationship. |
For More visit: DAX avoid using FILTER