Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Summing two measures with different filter rules

Hi All, 

 

I would like to create a measure that outputs a sum of two other measures, however the two measures have different filter rules( one measure needs to take into account the filter , while other needs to ignore it

 

Here is an example : 

 

Measure 1 WIP Orders = CALCULATE(Counta(Orders[ID]), FILTER(ALLEXCEPT(Orders [ Order Type], Orders[order raised date] <= MAX Order Raised Dates [ Date] && Orders[completed date]> MAX( Order Raised Dates [ Date]) || ISBLANK( Orders[Completed Date]))) - No order status filter applied

 

Measure 2 Cancelled Orders = CALCULATE(COUNTA(Orders[ID]), ALLEXCEPT(Orders[Status]) - No date filter applied

 

Measure 3 Combined total = WIP Orders + Cancelled Orders  ( with Order Status filter applied at visual level)

 

If I use measures 1 & 2 individually they are showing correct output, however in the measure 3 Order status filter seems to be applied to both measures is there a way to only apply order status filter to 1 measure when suming two ? 

 

I'm using live connection therefore cannot add any calculated columns.

 

Thanks

 

 

 

 

 

  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    Sorry I hadn't tested the measure before posting it. Please try the new one bellow. I created a relationship between 'Orders'[order raised date] and 'Order Raised Dates'[Date]. Also, the testing column [Group] can filter Measure 1. You can download the attachment to see details. 

    Measure 1 = 
VAR __maxOrderRaisedDate = MAX ( 'Order Raised Dates'[Date] )
RETURN
    CALCULATE (
        COUNTA ( Orders[ID] ),
        ALL ( Orders[Status] ),
        ALL ( 'Order Raised Dates'[Date] ),
        Orders[order raised date] <= __maxOrderRaisedDate,
        Orders[completed date] > __maxOrderRaisedDate || ISBLANK ( Orders[Completed Date] )
    )

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Some logic correction is required in 1st formula

     

    example


    CALCULATE(Counta(Orders[ID]), FILTER(allselected(Orders), [Order Type] = max([Order Type]) && Orders[order raised date] <= max(Orders[order raised date]) && ( Orders[completed date]> MAX( Order Raised Dates [ Date]) || ISBLANK( Orders[Completed Date])) ) )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks amitchandak  would you be able to explain the change ? As I have a few other columns that are included in Allexcept section of the measure which I kept it out for simplicity. When I tried to replace ALLEXCEPT with ALLSELECTED I am getting errors also why does MAX order type  need to be specified ? 

      Thanks

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous 

     

    Try avoid using ALLEXCEPT within FILTER function. Instead, you could try a mode like

    WIP Orders =
VAR __maxOrderRaisedDate =
    MAX ( 'Order Raised Dates'[ Date] )
RETURN
    CALCULATE (
        COUNTA ( Orders[ID] ),
        ALLEXCEPT ( Orders, Orders[ Order Type], Orders[xxxx] ),
        ( Orders[order raised date] <= __maxOrderRaisedDate
            && Orders[completed date] > __maxOrderRaisedDate )
            || ISBLANK ( Orders[Completed Date] )
    )

     

    I feel it difficult explaining the reason. You may refer to this thread (Filter and Allexcept) for a better explanation. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks v-jingzhang  I tried to tweak the formula but i am now getting error message : "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."  I read that including related statement might solve the problem however not sure where it would need to go given that I am using a variable ? 

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous 

         

        Sorry I hadn't tested the measure before posting it. Please try the new one bellow. I created a relationship between 'Orders'[order raised date] and 'Order Raised Dates'[Date]. Also, the testing column [Group] can filter Measure 1. You can download the attachment to see details. 

        Measure 1 = 
VAR __maxOrderRaisedDate = MAX ( 'Order Raised Dates'[Date] )
RETURN
    CALCULATE (
        COUNTA ( Orders[ID] ),
        ALL ( Orders[Status] ),
        ALL ( 'Order Raised Dates'[Date] ),
        Orders[order raised date] <= __maxOrderRaisedDate,
        Orders[completed date] > __maxOrderRaisedDate || ISBLANK ( Orders[Completed Date] )
    )

         

        Best Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as Solution to help other members find it.