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

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