Forum Discussion
1000000 limit hit for 6 data points
I understand the need for such a limit but I don't get why I'm hitting the limit when the visual only needs to populate 6 data point.
the measure is:
£ Receivable P&F (Closing) =
VAR _DateKey = [Dates.MaxPkDates]
RETURN SUMX(
FinancialCalculationsDaily
, IF(
FinancialCalculationsDaily[FkFinancialDate]=_DateKey
,FinancialCalculationsDaily[ReceivablePrincipal]+FinancialCalculationsDaily[ReceivableFees]
,BLANK()
)
)
(I tried using FILTER instead of putting it in the IF but that caused the same error)
I'm grouping it by year so it only needs to return 1 point per year and there are only 6 years of data in the DB hence 6 data points.
In my mind those are the only points it needs to return and 6 is less than 1,000,000 so I shouldn't be tripping the limit, can anyone explain why I'm tripping the limit and how I should do calculations like this to work within that limit?
Side note: I can't just do a sum and filter the date because that is open to user error if I let others access the dataset and it would mean I can show balance figures and revenue figures on the same graph as filtering to the last day would exclude rows from the revenue sum.
3 Replies
SUMX means it is iterating over the entire table. Can you do some sort of pre-filter to reduce the row count?
what exactly do you mean by "pre-filter"? if I filter the visual then the other metrics stop working. For example, filtering to year-end only would mean the revenue figures would be wrong as they would be missing the 364 days revenue prior to year-end.
or am I being dim and there's a way to filter in the dax? I don't understand how Microsoft want me to solve this and its an extremely basic use case, so I'm really quite confused.
£ Receivable P&F (Closing) = VAR _F = Filter(FinancialCalculationsDaily ,FinancialCalculationsDaily[FkFinancialDate]=[Dates.MaxPkDates] ) RETURN SUMX( _F ,FinancialCalculationsDaily[ReceivablePrincipal]+FinancialCalculationsDaily[ReceivableFees] )
