Forum Discussion
Advocate IINeed Help: SUMX with filter exceeds the ROW limit
I think you can add the time intelligence logic like
Sum less than 0 = VAR BaseTable = CALCULATETABLE ( ADDCOLUMNS ( FILTER ( KEEPFILTERS ( VALUES ( 'FactTable'[amount_base] ) ), 'FactTable'[amount_base] > 0 ), "@num rows", CALCULATE ( COUNTROWS ( 'FactTable' ) ) ), DATESBETWEEN ( DimTable[business_date], [opening_date_range], [closing_date_range] ) ) VAR Result = SUMX ( BaseTable, 'FactTable'[amount_base] * [@num rows] ) RETURN ResultBy wrapping the entire ADDCOLUMNS inside the CALCULATETABLE both the filtering and the count of the number of rows will be done in a filter context which is restricted to the relevant dates.
The thinking behind my approach was to limit the number of rows you have to iterate over to get the sum. By combining the value with the number of occurences you still get the right number but hopefully with fewer rows as there would be a smaller number of unique amounts. Given that it is still returning 5 million rows though that won't be enough.
As you are able to make changes to the model, I think that creating aggregation tables might be the best option, depending on how many dimensions you need to be able to slice by, and the cardinality of those dimensions. If you are just slicing by date then creating a snapshot of each day should be doable, to store the total amount < 0 and the total amount >0 for each day. Your measures would then simply sum this aggregation table.
For more on aggregation techniques you might find https://www.sqlbi.com/articles/optimizing-incremental-inventory-calculations-in-dax/ useful.
Super UserAnonymous appears to be copy and pasting AI-generated content.
The problem here might be that the formula engine is converting the filter condition FactTable[amount_base] < 0 into list of all negative amounts and applying that list as a filter. If there are more than 1 million distinct negative amounts, this causes the row limit problem.
The goal is to find another way of doing this that doesn't materialize a large filter table like that but it's not easy to control how the optimizer decides to do things.
Maybe try writing without a CALCULATE for this case and hope the optimizer can figure out how to run this without materializing the FILTER table.
VAR _Open = [opening_date_range]
VAR _Close = [closing_date_range]
VAR _Result =
SUMX (
FILTER (
FactTable,
FactTable[amount_base] < 0
&& FactTable[date_col] >= _Open
&& FactTable[date_col] <= _Close
),
FactTable[amount_base]
)
RETURN
_Result(You'll need to replace [date_col] with the appropirate name of the column your fact table.)
Advocate IIthanks buddy, unfortately I'm still getting the same error.
The funny part is I'm basically converting a report using DQ connecting to the Data source (Databricks) to use DQ connecting to a semantic model with data imported. Both approaches have the same data, and both are using DQ connection. The original formula worked in the DQ to Databricks but doesnt work with DQ to AS.