Forum Discussion
Super UserMeasure converted to Calculated column shows different result
Hi PBI Gurus,
I have below measure filtering Table Visual which gives me say 14 records for an ID.
Hi srlabhe ,
The query has exceeded resources message appears when a measure is calculated for each row in a large visual, often due to DAX functions like FILTER(ALL()) or complex logic that removes filters and forces full table scans. A calculated column behaves differently because it’s processed once during data refresh and stored in the model, while a measure is calculated dynamically based on active filters at runtime. To fix this, try simplifying your measure. For example, if your current measure looks like this:
MyMeasure =
VAR MaxDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
SUM('Sales'[Amount]),
FILTER(ALL('Calendar'), 'Calendar'[Date] <= MaxDate)
)Try replacing the FILTER(ALL()) part with KEEPFILTERS to make it more efficient:
MyMeasure_ =
VAR MaxDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
SUM('Sales'[Amount]),
KEEPFILTERS('Calendar'[Date] <= MaxDate)
)Power BI shows the query has exceeded resources message when a measure processes too much data, often due to functions like FILTER(ALL()) that force full table scans. Replace FILTER(ALL()) with KEEPFILTERS to respect existing filters and improve efficiency. For row-by-row logic such as running totals, use a lightweight calculated column to flag relevant rows and reference it in the measure. Simplify visuals by showing summary data instead of detailed rows, and if you’re using DirectQuery, consider switching to Import mode or using smaller summary tables. The difference between measures and calculated columns is normal because they’re evaluated at different times.
Thank you.
6 Replies
- Jihwan_Kim
Hi,
I think it is because of [MaxAsOfDateSelected] in the measure.
This measure is not dynamically applying to the calculated table.
If the dynamic result is needed based on the slicer selection, I think the best way is to implement measure, not calculated column.
Thank you.
- danextian
Hi srlabhe
Calculated columns and tables do not recognize filters applied through visuals—such as slicers, cross-filters from other visuals, or visual, page, and report-level filters. The [MaxAsOfDateSelected] in a calculated column is evaluated as though no filters are applied. For example, when using SELECTEDVALUE('Table'[Column]) in a calculated table or column, there is no single selected value within that context, so the function returns blank or when you use MAX instead, it returns the MAX value of that column regardless of existing filters in the report view.
srlabheANy solution for this because if I apply measure to table visual it breaks with Query has exceeded resources error
Hi srlabhe ,
The query has exceeded resources message appears when a measure is calculated for each row in a large visual, often due to DAX functions like FILTER(ALL()) or complex logic that removes filters and forces full table scans. A calculated column behaves differently because it’s processed once during data refresh and stored in the model, while a measure is calculated dynamically based on active filters at runtime. To fix this, try simplifying your measure. For example, if your current measure looks like this:
MyMeasure =
VAR MaxDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
SUM('Sales'[Amount]),
FILTER(ALL('Calendar'), 'Calendar'[Date] <= MaxDate)
)Try replacing the FILTER(ALL()) part with KEEPFILTERS to make it more efficient:
MyMeasure_ =
VAR MaxDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
SUM('Sales'[Amount]),
KEEPFILTERS('Calendar'[Date] <= MaxDate)
)Power BI shows the query has exceeded resources message when a measure processes too much data, often due to functions like FILTER(ALL()) that force full table scans. Replace FILTER(ALL()) with KEEPFILTERS to respect existing filters and improve efficiency. For row-by-row logic such as running totals, use a lightweight calculated column to flag relevant rows and reference it in the measure. Simplify visuals by showing summary data instead of detailed rows, and if you’re using DirectQuery, consider switching to Import mode or using smaller summary tables. The difference between measures and calculated columns is normal because they’re evaluated at different times.
Thank you.
Hi srlabhe ,
I would also take a moment to thank danextian , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions
