Forum Discussion

anis_keyna's avatar
anis_keyna
Frequent Visitor
8 years ago
Solved

Issues to convert data type text to date

Hi all,   Currently, I'm facing issues in converting data type to date. The data sources are from sql which have varchar data type. As for now, I could not change data type in date from sql. Theref...
Report Server
Reports
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    Hi anis_keyna,

     

    From the error message, the STATUS is a measure instead of calculated column, right? 

     

    You need to use aggregate function in the measure eg: MAX() 

     

    STATUS=IF(MAX('Shipment Planning'[ACT_MVT_DATE])<>BLANK(),"3",
    IF(MAX('Shipment Planning'[CONT_NO])<>BLANK(),"2",
    IF(MAX('Shipment Planning'[CONT_NO])=BLANK(),"1")))

     

    If you create a calculated column, then it's fine to use your own DAX. Please refer to this blog:https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

     

    In Power BI desktop, to convert TEXT date value to DATE type, you can select the date column, then click below option: 

     

     

    Best Regards,
    Qiuyun Yu 

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
8 years ago

Hi anis_keyna,

 

From the error message, the STATUS is a measure instead of calculated column, right? 

 

You need to use aggregate function in the measure eg: MAX() 

 

STATUS=IF(MAX('Shipment Planning'[ACT_MVT_DATE])<>BLANK(),"3",
IF(MAX('Shipment Planning'[CONT_NO])<>BLANK(),"2",
IF(MAX('Shipment Planning'[CONT_NO])=BLANK(),"1")))

 

If you create a calculated column, then it's fine to use your own DAX. Please refer to this blog:https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

 

In Power BI desktop, to convert TEXT date value to DATE type, you can select the date column, then click below option: 

 

 

Best Regards,
Qiuyun Yu 

anis_keyna's avatar
anis_keyna
Frequent Visitor
8 years ago

Thanks ! Its working now