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
