topic Re: Using a Boolean expression with CALCULATE in DAX Commands and Tips

Using a Boolean expression with CALCULATE

webportal — Mon, 31 Oct 2016 18:02:47 GMT

Can someone explain me why this expression of a calculated measure works:

Measure = CALCULATE(SUM(Facts[Turnover]);FILTER('Facts';'Facts'[UnitPrice]>=50))

Whereas this one doesn't:

Measure = CALCULATE(SUM(Facts[Turnover]);'Facts'[UnitPrice]>=50)

I know CALCULATE can use a Boolean expression or a table expression that defines a filter, but isn't the second expression using a Bolean condition:

'Facts'[UnitPrice]>=50

Re: Using a Boolean expression with CALCULATE

Anonymous — Thu, 03 Nov 2016 19:56:23 GMT

In what sense does the second formula "not work"? It appears to work fine in my test.

Re: Using a Boolean expression with CALCULATE

webportal — Thu, 03 Nov 2016 21:20:45 GMT

Hello,

I get the error:

This means something like: "A CALCULATE function was used in a TRUE/FALSE expression as a table filter. This is not allowed".

And this message gets me even more confused.

However, the following function works perfectly:

Re: Using a Boolean expression with CALCULATE

Anonymous — Thu, 03 Nov 2016 23:27:04 GMT

Are you adding that formula as a measure or a column?

Re: Using a Boolean expression with CALCULATE

webportal — Fri, 04 Nov 2016 09:03:08 GMT

As a measure.

Re: Using a Boolean expression with CALCULATE

LaurentCouartou — Fri, 04 Nov 2016 09:31:37 GMT

If 'Facts'[UnitPrice] is a calculated column, this expression

Measure = CALCULATE(SUM(Facts[Turnover])
                     ;'Facts'[UnitPrice]>=50
)

is just a shortcut for:

Measure = CALCULATE(SUM(Facts[Turnover])
                ;FILTER( ALL('Facts'[UnitPrice])
                    ;'Facts'[UnitPrice]>=50
                )
)

If 'Facts'[UnitPrice] is a measure, however, you will get an error message about CALCULATE not being allowed in a filter expression expression (of a CALCULATE expression).

Is it what is happening here?

Re: Using a Boolean expression with CALCULATE

webportal — Fri, 04 Nov 2016 09:36:38 GMT

Yes, 'Facts'[UnitPrice] is a measure.

Re: Using a Boolean expression with CALCULATE

LaurentCouartou — Fri, 04 Nov 2016 10:33:17 GMT

Then you have your answer. :manhappy:

Re: Using a Boolean expression with CALCULATE

webportal — Fri, 04 Nov 2016 14:27:52 GMT

Ok, so the answer is I can't use a calculated measure in CALCULATE but I can use in FILTER function, correct?

Re: Using a Boolean expression with CALCULATE

LaurentCouartou — Fri, 04 Nov 2016 17:03:03 GMT

This is correct. No measure or CALCULATE expression, unless you write a full FILTER expression around it.

Re: Using a Boolean expression with CALCULATE

Uderman — Wed, 25 Jun 2025 19:07:47 GMT

For a calculated column, you have the row context active, so row by row you can use the boolen expression. For the measure, there would be no row context active as it would be expected by the boolean expression.