Summing two measures with different filter rules

[Anonymous]

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]

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]

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 · Accepted solution]

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]

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

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