Dax optimization - Formula help

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