topic Re: Expressions that yield variant data-type cannot be used to define calculated columns in DAX Commands and Tips

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

Anonymous — Mon, 16 Sep 2024 12:36:16 GMT

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

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 type

Please help to solve this ?

Thanks

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

lbendlin — Mon, 16 Sep 2024 13:12:45 GMT

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.

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

Anonymous — Mon, 16 Sep 2024 14:08:50 GMT

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 .

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

lbendlin — Mon, 16 Sep 2024 14:22:41 GMT

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.

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

Anonymous — Mon, 16 Sep 2024 14:46:16 GMT

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

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

lbendlin — Mon, 16 Sep 2024 15:03:14 GMT

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

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

Anonymous — Mon, 16 Sep 2024 15:19:12 GMT

lbendlin Abbott account should be displayed in the table chart because it matched the condition (Forecast > 500k) , this is the expected result .

If i use SWITCH condition then the result is coming but also error occurs , if i use IF condition then the result is not coming (Abbott is missing) , so how to retrieve the correct result with any one of the above DAXs ?

For example :

Account	Offertype	forecast values	key accounts
AAA	Q	2334
BBB	Q	34556
CCC	A	675454
AAA	A	499523
DDD	Q	65454
BBB	A	9453

Row 1 : Account name is AAA, Offer type is Q and Forecast value is 2334
Row 4 : Account name is AAA , Offer type is A and Forecast value is 499523 then the sum of the AAA is >500K then it is "Yes". if row 1 & row 4 has less than 500K forecast value then it is "No"

Same logic for row 2 & 6 , I need to add this calculated column "Key Accounts" into page filter pane and select Yes or No in different bookmarks. So the account name should be unique with summing forecast values when it publishing in the table visual (no duplicate account names displaying)