Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to Solution.
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:
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:
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
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:
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:
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
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.
Can anyone please help here? Need some of your suggestions please.
User | Count |
---|---|
20 | |
20 | |
14 | |
10 | |
8 |
User | Count |
---|---|
29 | |
28 | |
13 | |
12 | |
11 |