Forum Discussion

Yaniv's avatar
Yaniv
Icon for Helper I rankHelper I
9 years ago
Solved

Insert blank value based on another field

Hi,   I have a table named 'service'. I want to create a new calucuated column that says if the field 'done' (date field) is blank do not insert any value but if the 'done' field is not blank inse...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi Yaniv,

     

    I changed the data type of Service[Done] from Text to Date/Time, delete and recreate the calculated column Service[DoneDate], everything worked.

     

    I have sent you the modified pbix file through private message.

     

    Best regards,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Yaniv,

 

For this formula:  NewColumn = IF(Service[Done]=BLANK()," ",Service[Done]+1)

As " " is a string value, while Service[Done]+1 returns date value, two different data types cannot exist in a single column. Otherwise, variant data-type error will prompt.

 

For this formula: NewColumn= IF(Service[Done]=BLANK(),BLANK(),Service[Done]+1)

It works fine and returns expected date in my test. I didn't the same error. So, in your scenario, were you creating calculated column in service table? How many tables and columns are referred to in your formula? Please provide more information so that I can reproduce your problem.

 

Best regards,
Yuliana Gu

Yaniv's avatar
Yaniv
Icon for Helper I rankHelper I
9 years ago

Hi v-yulgu-msft

 were you creating calculated column in service table? - Yes, the calcuated column is in the same table (service).

How many tables and columns are referred to in your formula? - Only 1 table and one column.

 

Thanks!!

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Yaniv,

     

    I cannot reproduce your problem. Please share some sample data so that I can test for you. Or, if possible, share your pbix file.

     

    Regards,
    Yuliana Gu

    • Yaniv's avatar
      Yaniv
      Icon for Helper I rankHelper I
      9 years ago

      I hope the attached screenshot will make it clear.

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi Yaniv,

         

        I noticed that in formula, the source column is Service[Done], but in data view, you pointed out that source column is [Created], why? Also, from the error message, what is the "in Table" column? Did you created any relationship between this table and another table?

         

        Regards,
        Yuliana Gu