Direct Query - transform Date TIme to Date

[martijnth]

Dear Reader,

I'm trying to confert a Desktop Dashboard to a Direct Query Dashoard since I could not find a proper way to refresh every 15 minutes automatically. Now in Desktop i used the following DAX code:

Shift_date = format(if((TBM_PRODUCTION_DATA[ProducedOn]-TBM_PRODUCTION_DATA[ProducedOn].[Date])<0.25,TBM_PRODUCTION_DATA[ProducedOn].[Date]-1,TBM_PRODUCTION_DATA[ProducedOn].[Date]),"dd-mm-yyyy")

In direct querry in cannot use the Format command, so I tried to change the format (ribbon) from Date/time (1/25/2019 1:23:29 PM) to Date (1/25/2019) when i plot the result in a table it actually only shows the Date, BUT when i plot as x-as in a graph all of a sudden it does not have seem to forgetten the time. I appreciate all solutions.

reg martijn

[v-danhe-msft · Accepted solution]

Hi @martijnth,

Could you have tried use the measure with FORMAT function? Based on my test, it could work on my side:

You can use the Format function in DQ mode if you go to Options -> DirectQuery and click "Allow unrestricted measures in DirectQuery mode".  Note that this will enable other functions whose performance is poor in DirectQuery mode, so save often.

Regards,

Daniel He

[martijnth]

Hi Daniel, thanks for your support, I made the changes in the options however, now I get the following notice:

something similar happens when I try to use RELATE in direct query:

the relation between the direct query SQL and the Related Excel is there....

Any advise is appreciated.

reg martijn

[martijnth]

copied wrong picture above,

[Anonymous]

Did you ever get a solution for this issue?

[martijnth]

well i actually solved it by using a native querry in SQL, something like

SELECT FORMAT (getdate (), 'dd/MM/yyyy ') as date

or look here:

https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

u use this command like:

than it works. Good luck

[Anonymous]

I dont have access to the database and was looking into implementing this change  using power query customr column