Forum Discussion

ghaines's avatar
ghaines
Resolver I
4 years ago

CALCULATE filters as filter function vs boolean expression

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?

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      ghaines
      Resolver I
      4 years ago

      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.

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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's avatar
      ghaines
      Resolver I
      4 years ago

      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.