cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper IV

## Dax optimization - Formula help

Hi,

The below logic is taking longer time to execute but giving me the right result though.
Can anyone tell me where am i going wrong?🙄

Overdue Days

Overdue Buckets 0-11

1 ACCEPTED SOLUTION
Super User

The reasons for performance issues look to be:

• Filtering tables rather than filtering columns. e.g. the two-column table created by ALL or 'DPO BSEG', . It's always best to filter columns individually where possible.
• Nesting of FILTERs by evaluating [Overdue Days.] within [0-10 Days aging].
• Multiple evaluations of [Overdue Days.]
• The SELECTEDVALUE functions on rows 2-3 of [Overdue Days.] are redundant, since if a single value is visible, then it is the maximum value anyway. So you can just use MAX expression for both of these.

Here is how I would propose to rewrite as a single measure, bearing in mind that I haven't been able to test it in your model:

``````0 - 10 Days aging =
VAR DaysAgingMin = 0
VAR DaysAgingMax = 10
VAR _endofmonth =
MAX ( 'Date Table'[End of the Month] )
VAR _selecteddate =
MAX ( 'Date Table'[Date] )
VAR DueDateLowerBound = _endofmonth - DaysAgingMax
VAR DueDateUpperBound = _endofmonth - DaysAgingMin
RETURN
CALCULATE (
SUM ( 'DPO BSEG'[WRBTR] ),
KEEPFILTERS (
'DPO BSEG'[AUGDT] = BLANK ()
|| _selecteddate < 'DPO BSEG'[AUGDT]
),
KEEPFILTERS ( _selecteddate >= 'DPO BSEG'[_Due Date] ),
KEEPFILTERS ( 'DPO BSEG'[_Due Date] <= DueDateUpperBound ),
KEEPFILTERS ( 'DPO BSEG'[_Due Date] >= DueDateLowerBound )
)
``````

My interpretation of what your original measure (and hopefully this measure) does is this (please correct me if I'm wrong):

Calculate the sum of WRBTR where:

• Due Date is on or before Selected Date
• AND Clearing Date (AUGDT) is either after Selected Date or blank
• AND Due Date is between 0-10 days before the End of the Month (being the same month as Selected Date)

I have combined the various filter conditions from your original measures into a single measure.

Does this produce the correct result, and does it perform better?

Regards,

Owen

Owen Auger
Blog
5 REPLIES 5
Super User

The reasons for performance issues look to be:

• Filtering tables rather than filtering columns. e.g. the two-column table created by ALL or 'DPO BSEG', . It's always best to filter columns individually where possible.
• Nesting of FILTERs by evaluating [Overdue Days.] within [0-10 Days aging].
• Multiple evaluations of [Overdue Days.]
• The SELECTEDVALUE functions on rows 2-3 of [Overdue Days.] are redundant, since if a single value is visible, then it is the maximum value anyway. So you can just use MAX expression for both of these.

Here is how I would propose to rewrite as a single measure, bearing in mind that I haven't been able to test it in your model:

``````0 - 10 Days aging =
VAR DaysAgingMin = 0
VAR DaysAgingMax = 10
VAR _endofmonth =
MAX ( 'Date Table'[End of the Month] )
VAR _selecteddate =
MAX ( 'Date Table'[Date] )
VAR DueDateLowerBound = _endofmonth - DaysAgingMax
VAR DueDateUpperBound = _endofmonth - DaysAgingMin
RETURN
CALCULATE (
SUM ( 'DPO BSEG'[WRBTR] ),
KEEPFILTERS (
'DPO BSEG'[AUGDT] = BLANK ()
|| _selecteddate < 'DPO BSEG'[AUGDT]
),
KEEPFILTERS ( _selecteddate >= 'DPO BSEG'[_Due Date] ),
KEEPFILTERS ( 'DPO BSEG'[_Due Date] <= DueDateUpperBound ),
KEEPFILTERS ( 'DPO BSEG'[_Due Date] >= DueDateLowerBound )
)
``````

My interpretation of what your original measure (and hopefully this measure) does is this (please correct me if I'm wrong):

Calculate the sum of WRBTR where:

• Due Date is on or before Selected Date
• AND Clearing Date (AUGDT) is either after Selected Date or blank
• AND Due Date is between 0-10 days before the End of the Month (being the same month as Selected Date)

I have combined the various filter conditions from your original measures into a single measure.

Does this produce the correct result, and does it perform better?

Regards,

Owen

Owen Auger
Blog
New Member

This works perfectly for the current measure, do we use it differently for the other buckets?

Kind Regards,

Siphiwe

Super User

Hi @Siphiwe

To generalise this formula to handle all buckets, I would recommend a Dynamic Segmentation pattern.

https://www.daxpatterns.com/dynamic-segmentation/

A Bucket table should be created with one row per bucket, containing DaysAgingMin and DaysAgingMax columns plus any required naming columns.

The measure above would then be adjusted to reference DaysAgingMin and DaysAgingMax values from that table.

Owen Auger
Blog
Helper IV

@OwenAuger , Thanks a lot. I got it.

That helps me in understanding where i am going wrong.

Helper IV

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors