Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 column but it did not work. Can you please let me know how can we change it.
Thanks
Solved! Go to Solution.
Hi @susheel1347 ,
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.
Hi @susheel1347 ,
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.
Hi @susheel1347 ,
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.
Hi @susheel1347 ,
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.
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
Hi @susheel1347 ,
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.
Hi,
Share the link from where i can download your PBI file.
Thanks for responding 😊. Can you please use excel with column value of '12/31/9999'? Since I cannot share the PBI file.
Thanks
Hi,
Even if we replace 12/31/9999 in the Date column, there will be mixed data types in that column and that is not adviseable.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |