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

Question

Hi Experts,&nbsp;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 = SWITCH(TRUE(),([Total FY Forecast] &gt; 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")here i am resulting only text (Yes or No) but i don't know how this error occurred.[Total FY Forecast] is Currency data type , rest all text data typePlease help to solve this ?&nbsp;ThanksDK

lbendlin · Answer

Your switch statement is malformed.
&nbsp;
&nbsp;
Key account Rule =
SWITCH (
    TRUE (),
    ( [Total FY Forecast] &gt; 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"
)
&nbsp;
You seem to have shifted the conditions and the values.
&nbsp;
Please provide a more detailed explanation of what you are aiming to achieve.&nbsp;
&nbsp;

anonymous · Answer

lbendlin&nbsp;Thanks for your response 🙂Actual requirement is below :&nbsp;Key Account Logic :-----------------if "Delivery model"="Embedded" or Forecast &gt; 500K ="Yes" or "PACE Account"="Yes" or "Engagement run with DPOT"="Yes" or "Account requires focus"="Yes" then it is key account.&nbsp;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.&nbsp;Already having a measure :&nbsp;Total FY Forecast = SUMX(VALUES('Test Delivery Updates'[FY Forecast]),CALCULATE(DISTINCT('Test Delivery Updates'[FY Forecast]))) --&gt; which is used in the SWITCH DAX .&nbsp;&nbsp;&nbsp;

lbendlin · Answer

&nbsp;
Key account Rule =
IF ( [Total FY Forecast] &gt; 500000  
     || [Delivery Model] = "Embedded" 
     || [PACE Account] = "Yes" 
     || [Engagement run with DPOT] = "Yes" 
     || [Account requires focus] = "Yes"
, "Yes","No"
)
&nbsp;
Note:&nbsp; You cannot (should not) use a measure to fill a calculated column. Calculated columns only have row context, not filter context.
&nbsp;

anonymous · Answer

lbendlin&nbsp; Error disappeared now when i used "IF" condition DAX but result is not as expected.&nbsp;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 &gt; 500k then it is Key Accounts else not a key account.&nbsp;If i use "SWITCH" condition , i can able to see the result but error occurs.&nbsp;Source :IF condition result : (FY forecast &gt; 500k)&nbsp;SWITCH condition result : (FY forecast &gt; 500k)&nbsp; --&gt; from source sum of 2 Abbott account forecast is &gt; 500k , so it is matched the condition. hence it is displaying in the table chart.&nbsp;&nbsp;DAX used in 2 different requirements:Key account Rule = SWITCH (TRUE (),&nbsp; &nbsp; ( [Total FY Forecast] &gt; 500000 ),&nbsp; &nbsp; "Yes"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || 'Test Delivery Updates'[Delivery Model] = "Embedded",&nbsp; &nbsp; "Yes"&nbsp; &nbsp; &nbsp; &nbsp; || 'Test Delivery Updates'[PACE Account] = "Yes",&nbsp; &nbsp; "Yes"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || 'Test Delivery Updates'[Engagement run with DPOT] = "Yes",&nbsp; &nbsp; "Yes"&nbsp; &nbsp; &nbsp; &nbsp; || 'Test Delivery Updates'[Account requires focus] = "Yes", &nbsp; &nbsp; "Yes")&nbsp;Key Account Rule 1 =VAR keyaccountrule = CALCULATE([Total FY Forecast],ALLEXCEPT('Test Delivery Updates','Test Delivery Updates'[Account Name]))RETURNIF(keyaccountrule &gt; 500000,"Yes","No")&nbsp;Hope it is understandable for you now ..

lbendlin · Answer

Looks like you just repeated your earlier (incorrect) code. What would you like to get assistance with?

Forum Discussion

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

6 Replies

Related content

m code data types create list of types

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

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

Conversion of date (data type) to text through dax expression

power query custom data type