Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

Hello, 

I tried to find out from the previous solutions from the forum. but I could not. I got that error.

May you help me to reach the solution?

 

Column 3 = IF(Forecast[CSR_Remarks -Delivery Forecast]="FOC Order",1,
IF(Forecast[CSR_Remarks -Delivery Forecast]="Order to be cancelled",2,
IF(Forecast[CSR_Remarks -Delivery Forecast]=BLANK(),3,
IF(MONTH(Forecast[CSR_Remarks -Delivery Forecast])=MONTH(TODAY()),"Current Forecast","Future Forecast")
)))
 
Thank you in advance 
Baris 
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    Wrap the 1, 2 and 3 in "" to make them text 🙂 

     

    Column 3 = IF(Forecast[CSR_Remarks -Delivery Forecast]="FOC Order","1",
    IF(Forecast[CSR_Remarks -Delivery Forecast]="Order to be cancelled","2",
    IF(Forecast[CSR_Remarks -Delivery Forecast]=BLANK(),"3",
    IF(MONTH(Forecast[CSR_Remarks -Delivery Forecast])=MONTH(TODAY()),"Current Forecast","Future Forecast")
    )))
     
    Just as an aside you can use SWITCH to write nest IFs:
    Column 3 =
    Switch(
    TRUE()
    ,Forecast[CSR_Remarks -Delivery Forecast]="FOC Order", "1"
    ,Forecast[CSR_Remarks -Delivery Forecast]="Order to be cancelled", "2"
    ,Forecast[CSR_Remarks -Delivery Forecast]=BLANK(), "3"
    ,MONTH(Forecast[CSR_Remarks -Delivery Forecast])=MONTH(TODAY()),"Current Forecast"
    ,"Future Forecast")
     
    The error is basically saying you cant have numbers and text in the same column
    By wrapping the numbers in "" or using the format function they become text like your text saying "Current forecast".
     

2 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    Wrap the 1, 2 and 3 in "" to make them text 🙂 

     

    Column 3 = IF(Forecast[CSR_Remarks -Delivery Forecast]="FOC Order","1",
    IF(Forecast[CSR_Remarks -Delivery Forecast]="Order to be cancelled","2",
    IF(Forecast[CSR_Remarks -Delivery Forecast]=BLANK(),"3",
    IF(MONTH(Forecast[CSR_Remarks -Delivery Forecast])=MONTH(TODAY()),"Current Forecast","Future Forecast")
    )))
     
    Just as an aside you can use SWITCH to write nest IFs:
    Column 3 =
    Switch(
    TRUE()
    ,Forecast[CSR_Remarks -Delivery Forecast]="FOC Order", "1"
    ,Forecast[CSR_Remarks -Delivery Forecast]="Order to be cancelled", "2"
    ,Forecast[CSR_Remarks -Delivery Forecast]=BLANK(), "3"
    ,MONTH(Forecast[CSR_Remarks -Delivery Forecast])=MONTH(TODAY()),"Current Forecast"
    ,"Future Forecast")
     
    The error is basically saying you cant have numbers and text in the same column
    By wrapping the numbers in "" or using the format function they become text like your text saying "Current forecast".
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from SamWiseOwl , please allow me to add some information: 
    Hi  Anonymous ,

     

    If it's convenient, could you provide your error message? Based on the DAX formula you provided, it appears that Forecast[CSR_Remarks - Delivery Forecast] is a text date. When using the Month() function, please be aware of the following points: the function uses the locale and date time settings of the client computer to understand the text value in order to perform the conversion. If the current date time settings represent a date in the format of Month/Day/Year, then the following string "1/8/2009" is interpreted as a datetime value equivalent to January 8th of 2009, and the function yields a result of 1. However, if the current date time settings represent a date in the format of Day/Month/Year, then the same string would be interpreted as a datetime value equivalent to August 1st of 2009, and the function yields a result of 8.

    MONTH function (DAX) - DAX | Microsoft Learn

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly