Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Akhil_1411
Helper IV
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 DaysOverdue Days

 

 

Overdue Buckets 0-11Overdue Buckets 0-11

 

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

5 REPLIES 5
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger 

 

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

 

Kind Regards,

Siphiwe

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
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@OwenAuger , Thanks a lot. I got it.

 

That helps me in understanding where i am going wrong.

Akhil_1411
Helper IV
Helper IV

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors