Forum Discussion

Mahipal's avatar
Mahipal
Icon for Helper I rankHelper I
2 years ago
Solved

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

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.

5 Replies

  • Mahipal's avatar
    Mahipal
    Icon for Helper I rankHelper I
    2 years ago

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

     

  • rajeshkumar12's avatar
    rajeshkumar12
    Regular Visitor
    2 years ago

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

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      I don't seem to have problems with that?

       

      item is a text type.

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.