calculate using sum and filter

[jpbi23]

I have this measure which sums up amount based on the filters applied in the formula. Then I subtract 'On hold service revenue' from 'Amount'

I have many formulas in the dataset like this. Visual (table) is taking too long to load after I implemented this logic. 

I'm wondering if there is a better way to write this formula which would perform better.

Remaining Months Recurring Services Revenue =

VAR SelectedDate = SELECTEDVALUE('Date'[PERIOD])

VAR EndDate = EDATE(SelectedDate, 60)

RETURN

CALCULATE(

    SUM(MASTER_REVENUE[AMOUNT]),

    FILTER(

        MASTER_REVENUE, MASTER_REVENUE[REVENUE_CATEGORY] in {"Recurring Services"} &&

        MASTER_REVENUE[PERIOD] > EndDate

        - MASTER_REVENUE[On hold Recurring Service]

    )

)

[jpbi23]

tahnk you for the reply! is there a better way to do the subtraction? Currently its in the filter. It needs to be,

Amount - On hold recurring service

[Anonymous · Accepted solution]

Hi @jpbi23 ,

I am glad to help you.
According to your description, I suggest you can create a virtual table in measure as an intermediate amount to increase the execution efficiency of dax, the modified code is as follows:

Remaining Months Recurring Services Revenue =
VAR SelectedDate =
    SELECTEDVALUE ( 'Date'[PERIOD] )
VAR EndDate =
    EDATE ( SelectedDate, 60 )
VAR table_ =
    FILTER (
        MASTER_REVENUE,
        MASTER_REVENUE[PERIOD] > EndDate - MASTER_REVENUE[On hold Recurring Service]
    )
RETURN
    CALCULATE (
        SUM ( MASTER_REVENUE[AMOUNT] ),
        FILTER ( table_, table_[REVENUE_CATEGORY] IN { "Recurring Services" } )
    )

For the optimization of the above code, I made test data and tested the performance of your previous dax code and the optimized dax code using virtual tables

Here is my test data (with 500 rows):

Write using virtual tables:

M_1 = 
VAR table_ =
    FILTER (
        Test_,
        Test_[saleTime] <=NOW()
    )
RETURN
    CALCULATE (
        SUM ( Test_[amount] ),
        FILTER ( table_, Test_[fruitName] IN { "apple" } )
    )

Virtual table writes are not used:

M_2 = 
    CALCULATE (
        SUM ( Test_[amount] ),
        FILTER ( Test_, Test_[fruitName] IN { "apple" } &&  Test_[saleTime] <=NOW() )
    )

I used each of these two MEASURES six times

The top six times are M_1

The bottom six times is M_2

The test results are as follows:

The average execution time for M_1 = 123ms
The average execution time for M_2 = 156ms

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.