a little clarification on the calculate function in dax

[db042190]

hi i'm reading the documentation on the calculate function at https://learn.microsoft.com/en-us/dax/calculate-function-dax and am wondering...

1) why filters can be separated by both boolean operators (&&,||) and / or commas?    notice the filter separating commas in the allowable syntax   CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]]).  Wouldnt one (boolean operators) or the other (commas) have been sufficient?

2) more importantly, the table filter "flavor" of allowable calculate filters (boolean, table filter, modifier) says "You can use the FILTER function to apply complex filter conditions, including those that cannot be defined by a Boolean filter expression.".  When , if ever would it be impossible to define a condition with a boolean filter expression?  can the community provide an example?

[v-cgao-msft · Accepted solution]

Hi @db042190 ,

1. The use of commas and boolean operators in the CALCULATE function is a matter of syntax and readability. The use of commas is a way to separate multiple filter arguments, while the use of boolean operators is a way to combine multiple conditions within a single filter argument. For example, you could write:

CALCULATE([Sales], 'Product'[Color] = "Red", 'Product'[Brand] = "Contoso")

 or

CALCULATE([Sales], 'Product'[Color] = "Red" && 'Product'[Brand] = "Contoso")

Both expressions would produce the same result, but the first one uses two separate filter arguments while the second one combines them into a single argument using the && operator.

2.  A table filter expression can be used to apply complex filter conditions that cannot be defined by a Boolean filter expression. For example, if you want to filter your data based on a condition that involves multiple columns from different tables, you would need to use a table filter expression. Here’s an example:

CALCULATE(
    [Sales],
    FILTER(
        Sales,
        RELATED(Product[Color]) = "Red" && RELATED(Product[Brand]) = "Contoso"
    )
)

In this example, we’re using the FILTER function to create a table filter expression that filters the Sales table based on conditions involving columns from the related Product table. This type of complex filtering cannot be achieved using a simple Boolean filter expression. Instead, we need to use a table filter expression created with the FILTER function.

Filter Arguments in CALCULATE - SQLBI
Specifying multiple filter conditions in CALCULATE - SQLBI

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[some_bih]

Hi @db042190 My understanding

1. there could be various form of writing filter argument in CALCULATE (everything after expression part) so this is generic desciption, like table in model

2. I would say, simple table expression is example, like SUMMARIZE (...). Filter argument as stated on link, picture below in article grouped into 3 parts and explained one by one.

Hope this help

[db042190]

thx, can the community provide the example i requested?  "When , if ever would it be impossible to define a condition with a boolean filter expression? "