Slow Performance Dax Measure, Can it be improved?

[Anonymous]

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

RETURN
SWITCH(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.

[Anonymous]

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!

[Anonymous]

Thank you! i will test out the speed.

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

[v-xuding-msft]

Hi @Anonymous ,

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.

[Anonymous]

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