Hi guys!
Looking to see if anyone can suggest an amend to this measure I am putting together in DirectQuery Mode to stop it failling due to row limitations? The aim of the calculation is to see what the payer rate % is of a live population of accounts, where a paying account is defined as having a positive amount of collections in month (So for example if an account has two transactions in month, one for £5.00 and one for -£5.00 it is not included). Example data as follows:
Base Data 500k rows:
DebtorNo | Live Date | Close Date |
| 1 | 01/01/2018 | 01/01/2019 |
| 2 | 01/06/2018 | 01/06/2019 |
| 3 | 01/09/2018 | 01/01/9999 |
| 4 | 01/01/2019 | 01/01/9999 |
Payment Data 2.3M Rows:
DebtorNo | EntryDate | Amount |
| 1 | 01/01/2019 | 10 |
| 2 | 01/02/2019 | 10 |
| 3 | 01/03/2019 | 15 |
| 3 | 10/03/2019 | -15 |
| 4 | 10/04/2019 | 30 |
There is also a standard dates table in the model that Base Data Live/Close Dates and Payement Data Date is related to.
Measure used:
Payer Rate % =
VAR RunningPlacedTotal =
CALCULATE (
COUNTA ( 'Base Data'[Live Month] ),
FILTER (
ALL ( 'Dates Base'[MonthName] ),
ISONORAFTER ( 'Dates Base'[MonthName], MAX ( 'Dates Base'[MonthName] ), DESC )
)
)
VAR RunningCloseTotal =
CALCULATE (
COUNTA ( 'Base Data'[Close Month] ),
USERELATIONSHIP ( 'Base Data'[Close Date], 'Dates Base'[DateID] ),
FILTER (
ALL ( 'Dates Base'[MonthName] ),
ISONORAFTER ( 'Dates Base'[MonthName], MAX ( 'Dates Base'[MonthName] ), DESC )
)
)
VAR RunningLiveTotal = RunningPlacedTotal - RunningCloseTotal
VAR ActualPayersRelationship =
CALCULATE (
COUNTROWS (
FILTER (
SUMMARIZE ( Payments, Payments[DebtorNo], "Total", SUM ( Payments[Amount] ) ),
[Total] > 0
)
),
USERELATIONSHIP ( Payments[EntryDate], 'Dates Base'[DateID] )
)
RETURN
DIVIDE ( ActualPayersRelationship, RunningLiveTotal, 0 )
The part where the measure breaks down is at the VAR = ActualPayersRelationship stage, as soon as anything more than about 10 months worth of transactions data is put through it it fails for exceeding the 1M row count limit. Is there anyway to tweak this part of the DAX to get around this issue while still getting the desired outcome whereby it doesn't error when additional data is put through it?
Cheers,
