Reply
Highlighted
Member
Posts: 124
Registered: ‎07-26-2016
Accepted Solution

Using a Boolean expression with CALCULATE

 

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

 


Accepted Solutions
Member
Posts: 108
Registered: ‎09-29-2016

Re: Using a Boolean expression with CALCULATE

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

View solution in original post


All Replies
Super Contributor
Posts: 1,130
Registered: ‎12-29-2015

Re: Using a Boolean expression with CALCULATE

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

Member
Posts: 124
Registered: ‎07-26-2016

Re: Using a Boolean expression with CALCULATE

Hello,

 

I get the error:

Capturar.JPG

 

 

 

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:

Capturar2.JPG


Super Contributor
Posts: 1,130
Registered: ‎12-29-2015

Re: Using a Boolean expression with CALCULATE

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

Member
Posts: 124
Registered: ‎07-26-2016

Re: Using a Boolean expression with CALCULATE

As a measure.

Member
Posts: 108
Registered: ‎09-29-2016

Re: Using a Boolean expression with CALCULATE

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?

Member
Posts: 124
Registered: ‎07-26-2016

Re: Using a Boolean expression with CALCULATE

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

Member
Posts: 108
Registered: ‎09-29-2016

Re: Using a Boolean expression with CALCULATE

Then you have your answer. Man Happy

Member
Posts: 124
Registered: ‎07-26-2016

Re: Using a Boolean expression with CALCULATE

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

Member
Posts: 108
Registered: ‎09-29-2016

Re: Using a Boolean expression with CALCULATE

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