Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Date column replace to NULL or NA

Hi All, I have a date column which has values 12-31-9999 in a table which I imported to Power BI desktop. I am trying to display NULL or NA for 12-31-9999. I have tried DAX calculation to create new...
Need Help
  • mahenkj2's avatar
    mahenkj2
    4 years ago

    Hi Anonymous ,

     

    I can just guess what problem you might be facing. I suggest to add some error description in your questions/responses. Sample data and desired outcome clarity is expected to get the answers asap.

     

    Below code works for me:

    Replaces = IF(FORMAT(DatesReplace[Dates],"dd-mm-yyyy")="31-12-9999", "NA","GoodDates")

     

    Output was like this:

     

     

    Since we can not compare date with text, so I had to use FORMAT function. You may also need to tweak as may be needed.

     

    Hope it helps.

     

     

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    The NULL or NA is text type. The data is date type. If we still want to change the column type.The error "Expressions that yield variant data-type cannot be used to define calculated columns" would appear.

    We can try mahenkj2 's way.

    Create a measure.

    mEASURE = IF(MAX('Table'[date])=DATE(9999,12,31),"NA","GOOD DATES")

    Or a column.

    Column = IF('Table'[date]=DATE(9999,12,31),"NA","GOOD DATES")

    If I have misunderstood your meaning, please provide your pbix file without privacy inforamtion and desired output.

     

    Best Regards

    Community Support Team _ Polly

     

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

mahenkj2's avatar
mahenkj2
Icon for Solution Sage rankSolution Sage
4 years ago

Hi Anonymous ,

I think, it is not good to have date and text in date column is not a good idea and probably it would not be even possible in DAX. Instead, make another column in the same table comparing with "12-31-9999" and if Yes then NA else say "GoodDates" and use this column as fliter/slicer in your report together with datecolumn you use. The similar thing you can do in Power query as well.

 

Hope it helps.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi mahenkj2 

    I tried creating new column and was comparing using IF condition in DAX. But I was not able to replace the value. Can you please let me know how can I compare and replace?

     

    Thanks

    • mahenkj2's avatar
      mahenkj2
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Hi Anonymous ,

       

      I can just guess what problem you might be facing. I suggest to add some error description in your questions/responses. Sample data and desired outcome clarity is expected to get the answers asap.

       

      Below code works for me:

      Replaces = IF(FORMAT(DatesReplace[Dates],"dd-mm-yyyy")="31-12-9999", "NA","GoodDates")

       

      Output was like this:

       

       

      Since we can not compare date with text, so I had to use FORMAT function. You may also need to tweak as may be needed.

       

      Hope it helps.