cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## calculate using sum and filter

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]
)
)
1 ACCEPTED SOLUTION
Community Support

Hi @jpbi23 ,

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.

2 REPLIES 2
Frequent Visitor

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

Community Support

Hi @jpbi23 ,

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.