cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dl_BL_PB Helper I
Helper I

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
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!

Highlighted
Dl_BL_PB Helper I
Helper I

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.

Dl_BL_PB Helper I
Helper I

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
New Ranks Launched March 24th!

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!

‘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

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.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors