topic Re: Expressions that yield variant data-type cannot be used to define calculated columns. in Developer

Expressions that yield variant data-type cannot be used to define calculated columns.

Venkata1989 — Thu, 05 Feb 2026 14:06:30 GMT

Hi Team,

Background of this bug:

We have three tables in the model: Date, Actuals, and Company. I am creating a calculated column in the Actuals table since it sits in the middle of the model and connects the other tables.

Requirement:

We have a Year slicer in the report.

When a user selects Year ≥ 2025, we need to exclude Company ID 28.
When a user selects Year < 2025, all companies, including Company ID 28, should be included.

Note:
This calculated column will be used in the “Filters on all pages” section. That is why I am working on implementing this logic through a calculated column. I need apply this filter around 25 Pages.

Calculated Column:

Exclude Company Row =

VAR YR = YEAR (RELATED ( 'Date'[Date] ))

VAR CompanyName =

RELATED ( 'Company'[Company ID and Name] )

RETURN

if(

YR < 2025,

CompanyName, CompanyName<>"28 sys"

)

============Below one belongs to static it's working=========

This solution currently works only as a static example using a calculated column. However, I need to implement this logic at a dynamic level, since we have more than 1,000 company IDs and cannot hardcode company names.

Below is the sample calculated column I used for testing:

Exclude Company Rows =

VAR YR = YEAR (RELATED ( 'Date'[Date] ))

VAR Company = RELATED ( Company[Company ID and Name] )

RETURN

IF (

YR < 2025 ,

Company IN {

"01 - IBM.",

"03 - CONZ"},

Company <> "28 - Wippro")

This approach is not scalable for the real requirement. We need a dynamic solution that responds to the Year slicer selection, instead of relying on fixed company values inside a calculated column.

Re: Expressions that yield variant data-type cannot be used to define calculated columns.

cengizhanarslan — Thu, 05 Feb 2026 14:00:01 GMT

Calculated columns cannot respond to a slicer. They’re computed at refresh time, not at query time. So a “Year slicer” can never dynamically change a calculated column. Instead create a measure that returns 1/0 based on selected year and use it as visual filter on each visual you have.

Include Company = VAR SelYear = SELECTEDVALUE ( 'Date'[Year] ) VAR IsBlockedCompany = SELECTEDVALUE ( Company[CompanyID] ) = 28 RETURN IF ( SelYear >= 2025 && IsBlockedCompany, 0, 1 )

Of course the solution below might not workd depending on your measure/visuals, the best solution here would be making these manipulations on your fact table.

Re: Expressions that yield variant data-type cannot be used to define calculated columns.

Venkata1989 — Thu, 05 Feb 2026 14:32:18 GMT

Hi @cengizhanarslan ,

I'm creating this calculated column under Fact table only.

Below one belongs to static it's working. But I need this static way to create. Because I have more than 1,000 company IDs and cannot hardcode company names.

Below is the sample calculated column I used for testing and working:

Exclude Company Rows =

VAR YR = YEAR (RELATED ( 'Date'[Date] ))

VAR Company = RELATED ( Company[Company ID and Name] )

RETURN

IF (

YR < 2025 ,

Company IN {

"01 - IBM.",

"03 - CONZ"},

Company <> "28 - Wippro")

Please guide me I need to make this dynamic way.

Re: Expressions that yield variant data-type cannot be used to define calculated columns.

Venkata1989 — Thu, 05 Feb 2026 16:03:47 GMT

Hi All,

I have created below calculated column, it's working fine.

5Exclude Company Flag =

VAR YR = YEAR ( RELATED ( 'Date'[Date] ) )

VAR CompanyID = RELATED ( Company[Company ID and Name] )

RETURN

IF (

YR >= 2025 && CompanyID = "28 - Wippro",

)