Latest Enterprise DNA Initiatives


How to utilise a Boolean expression within the CALCULATE function

DAX Calculations
#1

I’m having an issue with the CALCULATE function that I can’t quite solve.

This below seems to work fine.

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

But this one does not and I get a weird error

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

The error I receive is the below…

“A CALCULATE function was used in a TRUE/FALSE expression as a table filter. This is not allowed”.

Bit confused on this one because I believe that you could do either here and there is no problem.

Maybe I am confused regarding the use of measures inside of CALCULATE and/or FILTER. I’m not sure.

Thanks

#2

@EmGriff
The issue you are running into is that Calculate can take simple filters, like Product [Color] = “Red” or something similar. Once you introduce a more complex filter, Facts[UnitPrice] >= 50, calculate does “know” what to do with that. So you need to use Filter (which is an iterator, so it will iterate the facts table and keeping all the rows where Unit Price > 50) and that table will be aggregated over.

You can most definitely use measures in your filter statements, and honestly, you would be better off doing that more often than not because depending on your formula.

Nick

Enterprise%20DNA%20Expert%20-%20Small

#3

Yes agree with Nick on this one.

I think you’ll also find that you are likely using a measure here for Unit Price instead of a column.

So there’s a few places where this is falling over for you.

You need to use FILTER here, which is very easy to do and gives you a lot more flexibility in the calculations you are doing.

I actually use FILTER on nearly all occasions just because I know that it’s much more versatile. It’s really important to actually understand what FILTER is doing by iterating through the table that you give it. This is a very key concept to master early on with Power BI.

Please check out the below tutorials to learn more around this.

See how you go working through these ideas and techniques.

Thanks
Sam