Using FILTER() in DAX

Question

What is the best practice in applying filters with CALCULATE() function in Power BI? Using FILTER() as below: Most Expensive Non-Organic = CALCULATE(MAX(dProduct[Amount]), FILTER(dProduct, dProduct[IsOrganic]="N"))

Or, Using a direct Boolean Expression as below: Most Expensive Non-Organic = CALCULATE(MAX(dProduct[Amount]),'dProduct'[IsOrganic]="N")

score 1 · Answer 1 · answered Dec 30 '21 at 06:21

When you write a CALCULATE statement, all the filter arguments are table expressions, such as a list of values for one or more columns, or for an entire table. For example, when you write:

CALCULATE (
    <expression>,
    table[column] = <value> 
)

In reality the filter expression you wrote is transformed in:

CALCULATE (
    <expression>,
    FILTER (
        ALL ( table[column] ),
        table[column] = <value> 
    )
)

This behavior is identical for all the filter arguments of CALCULATE and CALCULATETABLE.

Your first statement is rather bad.

CALCULATE(MAX(dProduct[Amount]), FILTER(dProduct, dProduct[IsOrganic]="N"))

correct way for use filter in your example:

CALCULATE(MAX(dProduct[Amount]), FILTER(ALL(dProduct[IsOrganic]), dProduct[IsOrganic]="N"))

because you provide as filter a full EXPANDED table (you push many column from dProduct and related table).

Read this article: https://www.sqlbi.com/articles/expanded-tables-in-dax/

Using FILTER() in DAX

1 Answers1