topic Re: Dax optimization - Formula help in DAX Commands and Tips

Dax optimization - Formula help

Akhil_1411 — Thu, 08 Apr 2021 13:17:57 GMT

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?🙄

Re: Dax optimization - Formula help

Akhil_1411 — Fri, 09 Apr 2021 13:09:07 GMT

Can anyone please help here? Need some of your suggestions please.

Re: Dax optimization - Formula help

OwenAuger — Fri, 09 Apr 2021 13:17:48 GMT

Hi Akhil_1411

From my reading of your measures, I would recommend changing to a single measure instead.

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

Re: Dax optimization - Formula help

Akhil_1411 — Tue, 13 Apr 2021 06:51:20 GMT

OwenAuger , Thanks a lot. I got it.

That helps me in understanding where i am going wrong.

Re: Dax optimization - Formula help

Siphiwe — Fri, 30 Jun 2023 15:32:05 GMT

Hi OwenAuger

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

Kind Regards,

Siphiwe

Re: Dax optimization - Formula help

OwenAuger — Sun, 02 Jul 2023 23:43:37 GMT

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.