Calculate cumulative total in DAX?

Question

I'm calculating cumulative total in DAX like:

DEFINE MEASURE 'Sales'[Running Total] = 
CALCULATE (
  SUM('Sales'[Revenue]),
  FILTER(ALL('Date'[Date]),'Date'[Date]<=MAX('Date'[Date]))
)

This should be well-estabilished pattern (at least it is referenced here: http://www.daxpatterns.com/cumulative-total/)

My problem is when I try to evaluate it like:

EVALUATE SUMMARIZECOLUMNS(
  'Date'[Date],
  "Total_Revenue_By_Date", 
  'Sales'[Running Total]
)

I'm running into error

The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

I'm using tabular model with direct query. I know I can enlarge the limit, however the underlying tables are small - Date table has around 10000 rows, Sales table has around 10000 rows as well (it will be much larger on production), so something here doesn't scale well.

I have an idea how to get away with calculating running totals on SQL level, any idea how to tackle this on DAX level ?

Jigar Jigar · Accepted Answer · 2017-06-12T21:38:37

Models created by Power BI desktop has default limit of 1 million rows.

This might help you, https://www.sqlbi.com/articles/tuning-query-limits-for-directquery/

Calculate cumulative total in DAX?

1 Answers