Forum Discussion
Helper VCreate a date-time field from a date column and a number column with the time in "DirectQuery Mode"
Community SupportHi 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.
Helper VI 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
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
I understand. Thank you very much for the support, and I will consult with the database team about the possibility of adding the information.
- V-yubandi-msft
Sure, telesforo1969 . Once you connect with the database team , please let us know whether it’s possible or not. For further investigation, we’ll proceed based on the database team’s inputs.
Thanks for staying engaged with the community.