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 c...
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
        )
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
    )