topic Re: CALCULATE filters as filter function vs boolean expression in DAX Commands and Tips

CALCULATE filters as filter function vs boolean expression

ghaines — Tue, 15 Mar 2022 00:05:43 GMT

I just fixed an issue and I don't understand how.

The brief was a unit count above and below our theoretical "minimum price" for a new order.

I used a simple boolean in a CALCULATE function:

ASPStratifiedUnitsReturnValue = //Accounts for some deprecated items with the same product type VAR HighestIndexInItemType = MAX('Pricing at Divisions'[ITEM_ID]) VAR PlatinumPrice = CALCULATE(MIN('Pricing at Divisions'[ITEM_UNIT_PRICE]), FILTER('Pricing at Divisions', 'Pricing at Divisions'[ITEM_ID] = HighestIndexInItemType) ) RETURN SWITCH(SELECTEDVALUE('_ASP Stratified Headings'[Ordering]), 1, CALCULATE([_UnitSalesFin], Transactions[Net Amount] >= PlatinumPrice ), 2, CALCULATE([_UnitSalesFin], Transactions[Net Amount] < PlatinumPrice ), BLANK() )

It was to run in the context of a matrix with

Product
- Platinum+ and below Platinum strata

The 'Pricing at Divisions' table is only filtered by the product table.

This code did not work as intended, instead not applying the filters at all and returning the same value regardless of the headings.

If I changed to using the FILTER function e.g.

CALCULATE([_UnitSalesFin], FILTER(Transactions, Transactions[Net Amount] >= PlatinumPrice) )

then it worked as intended.

What is the difference?

Re: CALCULATE filters as filter function vs boolean expression

amitchandak — Tue, 15 Mar 2022 03:56:32 GMT

ghaines , I think this one should use allselected

VAR PlatinumPrice = CALCULATE(MIN('Pricing at Divisions'[ITEM_UNIT_PRICE]), FILTER(allselected('Pricing at Divisions'), 'Pricing at Divisions'[ITEM_ID] = HighestIndexInItemType)

Re: CALCULATE filters as filter function vs boolean expression

ghaines — Tue, 15 Mar 2022 04:10:41 GMT

amitchandak Thanks for your reply.

It didn't fix the measure when the filters were changed back to simple booleans.

In any case, whatever that intermediate variable was evaluating to, it doesn't seem to have any effect on the return value.

The restriction Net_Amount >= PlatinumPrice returned all transactions in the context

The restriction Net_Amount < PlatinumPrice returned all transactions in the context

The value of PlatinumPrice is immaterial. There is a mistake contained somewhere inside the Switch statement, and probably within a calculate function, since the default value of the switch was not returned except where appropriate.

Re: CALCULATE filters as filter function vs boolean expression

v-kkf-msft — Thu, 17 Mar 2022 07:21:02 GMT

Hi ghaines ,

A Boolean expression used as a filter parameter in a CALCULATE function corresponds to an equivalent FILTER expression that operates on all the values of a column.

This means that Equation

CALCULATE([_UnitSalesFin], Transactions[Net Amount] < PlatinumPrice )

is actually equivalent to Equation

CALCULATE([_UnitSalesFin], FILTER( ALL(Transactions[Net Amount]), Transactions[Net Amount] < PlatinumPrice ) )

For more information, please refer to: How CALCULATE works in DAX

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: CALCULATE filters as filter function vs boolean expression

ghaines — Fri, 25 Mar 2022 04:47:19 GMT

Hi v-kkf-msft ,

As mentioned in the question, I am getting different results between the filter method and the boolean expression. That's why I am making the post. When I use the boolean expression, my data is not filtered.