Forum Discussion
Error cannot be pushed to the remote data source and cannot be used in this scenario
Hi,
I have two tables:
- Transaction table from a separate Power BI Semantic model (t_sales)
- Local Date table as slicer (dim_date)
I'd like to add a calc column "ActiveFlag" with folowing formula
This DAX gives me the above error and Data Import option is not possible.
Appreciate any feedback.
Thanks.
hcze
Calculated columns are static — They are evaluated row-by-row at model refresh/load time (in row context), not dynamically based on slicer selections (filter context). SELECTEDVALUE(dim_Date[Date]) returns a value based on the current filter context from a visual/slicer, which doesn't exist when the column is being calculated.Composite model limitations — Your t_sales table comes from a remote Power BI semantic model (DirectQuery), while dim_date is local (Import mode). In composite models:
- Calculated columns on remote tables have very strict limits (mostly simple intra-row operations).
- You cannot easily mix local and remote tables in calculated columns, especially with functions like SELECTEDVALUE
I suggest you to switch your logic to a measure — it evaluates dynamically in the filter context of the visual/slicer.
Active Flag = VAR _SelectedDate = SELECTEDVALUE(dim_Date[Date]) RETURN IF( ISBLANK(_SelectedDate), BLANK(), // or "Unknown" / TRUE() depending on your needs t_sales[cancellationdate] > _SelectedDate )
Use this measure in visuals (e.g., as a filter, in a table/matrix, for conditional formatting, or in other calculations).
And for counting/summing active transactions you can use something like this:
Active Transactions = CALCULATE( COUNTROWS(t_sales), [Active Flag] = TRUE() )
4 Replies
hcze
Calculated columns are static — They are evaluated row-by-row at model refresh/load time (in row context), not dynamically based on slicer selections (filter context). SELECTEDVALUE(dim_Date[Date]) returns a value based on the current filter context from a visual/slicer, which doesn't exist when the column is being calculated.Composite model limitations — Your t_sales table comes from a remote Power BI semantic model (DirectQuery), while dim_date is local (Import mode). In composite models:
- Calculated columns on remote tables have very strict limits (mostly simple intra-row operations).
- You cannot easily mix local and remote tables in calculated columns, especially with functions like SELECTEDVALUE
I suggest you to switch your logic to a measure — it evaluates dynamically in the filter context of the visual/slicer.
Active Flag = VAR _SelectedDate = SELECTEDVALUE(dim_Date[Date]) RETURN IF( ISBLANK(_SelectedDate), BLANK(), // or "Unknown" / TRUE() depending on your needs t_sales[cancellationdate] > _SelectedDate )
Use this measure in visuals (e.g., as a filter, in a table/matrix, for conditional formatting, or in other calculations).
And for counting/summing active transactions you can use something like this:
Active Transactions = CALCULATE( COUNTROWS(t_sales), [Active Flag] = TRUE() )
You can't use SELECTEDVALUE() in a calculated column. Columns are evaluated at refresh time; there’s no slicer selection happening yet, so it always returns BLANK. This isn't a composite model issue; it's just how DAX works.
Since t_sales comes from an external semantic model, you also can't add calculated columns to it directly, and Import is off the table. So even if the formula worked, you'd have nowhere to put it.
This kind of slicer-aware logic has to live in a measure. Something like:ActiveFlag = VAR _selectedDate = SELECTEDVALUE(dim_Date[Date], MAX(dim_Date[Date])) VAR _cancelDate = MAX(t_sales[cancellationdate]) RETURN IF(_cancelDate > _selectedDate, 1, 0)Hi hcze , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.
