Forum Discussion

telesforo1969's avatar
telesforo1969
Icon for Helper V rankHelper V
10 months ago

Create a date-time field from a date column and a number column with the time in "DirectQuery Mode"

  I need help joining two columns, one containing values ​​in date format and the other containing the time in number format in DirectQuery mode.  
Need Help
telesforo1969's avatar
telesforo1969
Icon for Helper V rankHelper V
10 months ago

Thank you very much for the support, but your proposal yields the following result: it changes the dates and always shows the time as 12:00:00

V-yubandi-msft's avatar
V-yubandi-msft
Icon for Community Support rankCommunity Support
10 months ago

Hi telesforo1969 ,

Thank you for testing, you're doing a good job exploring different solutions. You’re correct if the [Hora] column contains just a number, the DAX formula may default to 12:00:00, which is not the intended result.

To resolve this, we should adjust the formula to match the format of your [Hora] column. Here are a few options based on how your data is set up.
1. If [Hora] is a whole number

DateTime = 
'Table'[Fecha] + TIME(INT('Table'[Hora]), 0, 0)

2. If [Hora]  is already in time format

DateTime = 
'Table'[Fecha] + 'Table'[Hora]

 3.If [Hora] includes decimals

DateTime = 
'Table'[Fecha] + TIME(INT('Table'[Hora]), MOD('Table'[Hora]*60, 60), 0)

 

Try and Let me know how your [Hora] column is set up whether it's an integer, decimal, or actual time. You’re really close to getting this working smoothly.

 

Regards,
Yugandhar.

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    10 months ago

    Hi telesforo1969 ,

    Have you had a chance to try the suggested solution? Please let us know if the issue is resolved or if you need any additional details.  We’re happy to help if you need further assistance.

     

     

    Thank you.

  • telesforo1969's avatar
    telesforo1969
    Icon for Helper V rankHelper V
    10 months ago

    I added an additional step because I had to change my hour period (1-24) to (0-23), but the format sets it as shown in the images. When I change it to integer type, it doesn't allow me.

     

    • V-yubandi-msft's avatar
      V-yubandi-msft
      Icon for Community Support rankCommunity Support
      10 months ago

      Hi telesforo1969 ,

      Thank you for your time and the update. In DirectQuery mode, changing data types or adding columns isn’t supported. It’s best to update your SQL source using DATEADD(HOUR, Hora - 1, Fecha) or switch to a Composite model and use DAX to create the DateTime column.

      Hope this helps resolve the issue.

       

      Regards,
      Yugandhar.

      • telesforo1969's avatar
        telesforo1969
        Icon for Helper V rankHelper V
        10 months ago

        I understand. Thank you very much for the support, and I will consult with the database team about the possibility of adding the information.