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.

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.