Measure converted to Calculated column shows different result

[srlabhe]

Hi PBI Gurus,

I have below measure filtering Table Visual which gives me say 14 records for an ID.

ToShow =

if(

CALCULATE(

    MAX(Table[AS_OF_DATE]), // Returns the latest As Of Date

    FILTER(

        ALL(Table),

        Table[ID] = SELECTEDVALUE(Table[ID]) && // Filter for the current ID 

        Table[AS_OF_DATE] <= [MaxAsOfDateSelected] // Filter for dates before the selected slicer date

    )

)=SELECTEDVALUE(Table[AS_OF_DATE]),1,0)

When I convert this measure to calculated column like ToShowCol=[Toshow] and filter the table visual the resultset changes to 13 recs. One of the recs shows value of ToshowCol as 0 while for record for measure ToShow is 1 Any ideas why is it happening?

Below is result 

Thanks

[v-sshirivolu]

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

[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.

[srlabhe]

ANy solution for this because if I apply measure to table visual it breaks with Query has exceeded resources error 

[v-sshirivolu · Accepted solution]

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.

[v-sshirivolu]

Hi @srlabhe ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[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.