cancel
Showing results for
Did you mean:
Helper II

## Slow Performance Dax Measure, Can it be improved?

Hello all,

I have terrible performance in my disbursement close measure. I was wondering if my measure can be improved?

Please note , i am running this measure in a tabular model which great hardware with a star schema. The WIP fact table has about 15 million rows).

`Disbursement Close :=VAR DisbClose = Calculate(Sum('Wip History'[WIP]),FILTER('Transaction Types','Transaction Types'[Is_Disbursement_Flag] = 1||'Transaction Types'[Is_Disbursement_Write_Off_Flag] = 1),FILTER(ALL( 'Tran Date'), 'Tran Date'[Tran Date]<= MAX ( 'Tran Date'[Tran Date] )))- ( - Calculate(Sum('Wip History'[WIP]),'Transaction Types'[Is_Disbursement_Fee_Flag] = 1,FILTER(ALL( 'Tran Date'), 'Tran Date'[Tran Date]<= MAX ( 'Tran Date'[Tran Date] ))))RETURNSWITCH(TRUE(), (DisbClose < 1 && DisbClose > -1) ,BLANK() , DisbClose)`

Measure breakdown:

I have a Measure which calculates the

1. Disb Close = (Disbursements + write offs) - (Disbursement Fee * -1).

2. It also needs to filter the result so the rows Transaction Date is less than the max transaction date (filtering purposes)

3. Due to this table being a balancing table, sometimes the result is 0.002, or 0.005 once it is aggregated to a higher level like to a matter level. So i also have to check if it is "SWITCH(TRUE(), (DisbClose < 1 && DisbClose > -1) ,BLANK() , DisbClose)" so if the result is less than 1 AND greater than -1 then set it to blank.

4 REPLIES 4
Highlighted
Resolver III

## Re: Slow Performance Dax Measure, Can it be improved?

One thing you can do to help is write another variable for the table that you're using for the sum, and include the filter for MAX transaction date, like this:

``````VAR filtered_dates =
CALCULATE ( SUM('Wip History'[WIP]),
FILTER( ALL( 'Tran Date'),
'Tran Date'[Tran Date] <= MAX ( 'Tran Date'[Tran Date] )
)
)``````

Then substitue that variable in:

``````Disbursement Close :=
VAR filtered_dates =
CALCULATE ( SUM('Wip History'[WIP]),
FILTER( ALL( 'Tran Date'),
'Tran Date'[Tran Date] <= MAX ( 'Tran Date'[Tran Date] )
)
)
VAR DisbClose = Calculate(filtered_dates,FILTER('Transaction Types','Transaction Types'[Is_Disbursement_Flag] = 1||'Transaction Types'[Is_Disbursement_Write_Off_Flag] = 1))
-
( - Calculate(filtered_dates,'Transaction Types'[Is_Disbursement_Fee_Flag] = 1))

RETURN
SWITCH(TRUE(), (DisbClose < 1 && DisbClose > -1) ,BLANK() , DisbClose)``````

Not sure how much it will help, but it might speed it up a tiny bit!

Helper II

## Re: Slow Performance Dax Measure, Can it be improved?

Thank you! i will test out the speed.

Edit----- It actually produces a different result!

Community Support

## Re: Slow Performance Dax Measure, Can it be improved?

Hi @Dl_BL_PB ,

Do you resolve the problem? You could reference the tips of  improving Power BI Performance by Optimizing DAX  to have a try.

Best Regards,

Xue Ding

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

Helper II

## Re: Slow Performance Dax Measure, Can it be improved?

No, the problem has not been resolved. Thanks for the knowledge article! Unfortunetly, i did not find anything that can relate to my query.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors