CALCULATE filters as filter function vs boolean expression

[ghaines]

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?

[v-kkf-msft]

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.

[ghaines]

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.

[amitchandak]

@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)

[ghaines]

@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.