Forum Discussion

Venkata1989's avatar
Venkata1989
New Member
6 months ago
Solved

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

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.

General Comment
Need Help
Show and Tell
Tips and Tricks
  • Venkata1989's avatar
    Venkata1989
    6 months ago

    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's avatar
    Venkata1989
    6 months ago

    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
        )

3 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    6 months ago

    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's avatar
      Venkata1989
      New Member
      6 months ago

      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's avatar
    Venkata1989
    New Member
    6 months ago

    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
        )