Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

Hi Experts,   I am getting this error "Expressions that yield variant data-type cannot be used to define calculated columns" when using SWITCH(TRUE()) condition as below . Key account Rule = ...
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

Your switch statement is malformed.

 

 

Key account Rule =
SWITCH (
    TRUE (),
    ( [Total FY Forecast] > 500000 ),
        "Yes"
            || 'Test Delivery Updates'[Delivery Model] = "Embedded",
    "Yes"
        || 'Test Delivery Updates'[PACE Account] = "Yes",
        "Yes"
            || 'Test Delivery Updates'[Engagement run with DPOT] = "Yes",
    "Yes"
        || 'Test Delivery Updates'[Account requires focus] = "Yes", "Yes"
)

 

You seem to have shifted the conditions and the values.

 

Please provide a more detailed explanation of what you are aiming to achieve. 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    lbendlin Thanks for your response 🙂

    Actual requirement is below :

     

    Key Account Logic :

    -----------------

    if "Delivery model"="Embedded" or Forecast > 500K ="Yes" or "PACE Account"="Yes" or "Engagement run with DPOT"="Yes" or "Account requires focus"="Yes" then it is key account.

     

    All the accounts are having the above columns and if any one of the conditions is matched then the calcluated column will give YES else NO . I need to use this calculated column to filter pane to choose YES or NO.

     

    Already having a measure : 

    Total FY Forecast = SUMX(VALUES('Test Delivery Updates'[FY Forecast]),CALCULATE(DISTINCT('Test Delivery Updates'[FY Forecast]))) --> which is used in the SWITCH DAX .
     
     

     

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

       

      Key account Rule =
IF ( [Total FY Forecast] > 500000  
     || [Delivery Model] = "Embedded" 
     || [PACE Account] = "Yes" 
     || [Engagement run with DPOT] = "Yes" 
     || [Account requires focus] = "Yes"
, "Yes","No"
)

       

      Note:  You cannot (should not) use a measure to fill a calculated column. Calculated columns only have row context, not filter context.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        lbendlin  Error disappeared now when i used "IF" condition DAX but result is not as expected.

         

        Actually we have to create 2 different Key Account logic columns to filter the accounts , I am using sharepoint list as a data source , in the list I have created a duplicate account and testing it if same account has 2 different forecast values even 2 different offer type and once it added into the table visual it should give only one account with sum of forecast values , if those sum value > 500k then it is Key Accounts else not a key account.

         

        If i use "SWITCH" condition , i can able to see the result but error occurs.

         

        Source :

        IF condition result : (FY forecast > 500k)

         

        SWITCH condition result : (FY forecast > 500k)  --> from source sum of 2 Abbott account forecast is > 500k , so it is matched the condition. hence it is displaying in the table chart.

         

         DAX used in 2 different requirements:

        Key account Rule = SWITCH (TRUE (),
            ( [Total FY Forecast] > 500000 ),
            "Yes"
                    || 'Test Delivery Updates'[Delivery Model] = "Embedded",
            "Yes"
                || 'Test Delivery Updates'[PACE Account] = "Yes",
            "Yes"
                    || 'Test Delivery Updates'[Engagement run with DPOT] = "Yes",
            "Yes"
                || 'Test Delivery Updates'[Account requires focus] = "Yes",
            "Yes"
        )
         
        Key Account Rule 1 =VAR keyaccountrule = CALCULATE([Total FY Forecast],ALLEXCEPT('Test Delivery Updates','Test Delivery Updates'[Account Name]))
        RETURN
        IF(keyaccountrule > 500000,"Yes","No")
         
        Hope it is understandable for you now ..