Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?🙄
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 |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |