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

[Venkata1989]

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.

[Venkata1989 · Accepted solution]

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")

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.

Please guide me I need to make this dynamic way.

[Venkata1989 · Accepted solution]

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",

        0,

        1

    )

[cengizhanarslan]

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.

[Venkata1989 · Accepted solution]

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")

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.

Please guide me I need to make this dynamic way.