Converting from datatype Date/Time column to Date using DAX in Direct query

[Mahipal]

Hello Everyone,

We are using a column which is date/time datatype. Which we need to use to create date column using the DAX expression with calculated column in Direct Query. 

Joining date-   3/6/2021 4:18:56 PM 

We want out put to be just date column like 3/6/2021 (Date datatype).

Below is what we tried,

Column3 = COMBINEVALUES("/",MONTH(Employee[Joining date]),DAY(Employee[Joining date]),YEAR(Employee[Joining date])).

We are getting output as text value, but we need it as Date datatype because sorting is not working. 

Any suggestions to achieve this.

[Anonymous · Accepted solution]

Hi @Mahipal ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table. Click on Transform Data.

2.Create the custom column to convert the Date/Time column to Date type.

= Text.From(Date.Month([Joining date]))&"/"&Text.From(Date.Day([Joining date]))&"/"&Text.From(Date.Year([Joining date]))

3.The Date type custom is shown below.

Solved: Change date format in DIRECT Query Mode - not on l... - Microsoft Fabric Community

Best Regards,

Wisdom Wu

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

[rajeshkumar12]

is there any way of converting a text (2/3/2024) to date datatype in direct query mode

[lbendlin]

I don't seem to have problems with that?

item is a text type.

[Mahipal]

DATEVALUE(), REPT(),FORMAT() and few more functions are not working for Directquery mode.

[lbendlin]

Have you tried DATEVALUE() ?  Or DATE() ?