cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dl_BL_PB Helper II
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] ))))

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.

 

4 REPLIES 4
Highlighted
cstaulbee Resolver III
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!

Dl_BL_PB Helper II
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
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.

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.
Dl_BL_PB Helper II
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.

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors