topic Re: DateDiff with Direct Query as Measure in DAX Commands and Tips

DateDiff with Direct Query as Measure

MarionKeller — Thu, 04 Jul 2019 08:34:50 GMT

I need to have the Date Difference between two Date columns

# of Minutes = Datediff(OP_ORDER_ITEM_ARCHIVE[PICKING_AT];OP_ORDER_ITEM_ARCHIVE[REFILLING_AT];MINUTE)

I'm using the DateDiff Function but it gives me the following error message:

Query(1,1) Function 'DATEDIFF' is not allowed as part of calculated column DAX expressions on Direct Query models

Can I use it as measure, I know i need to filter the Results somehow by row.

Any help is appreciated

Re: DateDiff with Direct Query as Measure

luke611 — Sat, 20 Jun 2020 20:43:02 GMT

I have exactly the same. DirectQuery + Oracle database. Any ideas?

Re: DateDiff with Direct Query as Measure

lbendlin — Sat, 20 Jun 2020 21:40:33 GMT

DATEDIFF is overrated. Try just subtracting one date from the other. Internally dates are treated as numbers with the whole part representing the days and the fraction representing, well fractions of days. Say you want to know the difference between Date A and Date B in hours, you would write ([Date A]-[Date B])*24

Re: DateDiff with Direct Query as Measure

luke611 — Mon, 22 Jun 2020 08:25:14 GMT

lbendlin many thanks for swift reply. Unfortunately there is another error raised when I change DATEDIFF into simple minus between two dates:

ORA-00904: "t0"."DATE_COLUMN": invalid identifier. The exception was raised by the IDbCommand interface.

Re: DateDiff with Direct Query as Measure

Anonymous — Mon, 22 Jun 2020 09:09:46 GMT

HI MarionKeller ,

You can have a look at this blog

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397

https://community.powerbi.com/t5/Desktop/DATEDIFF-in-Direct-Query-Mode-32-bit/m-p/492878

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: DateDiff with Direct Query as Measure

lbendlin — Mon, 22 Jun 2020 14:49:56 GMT

As you can see this is an error on the Oracle side. Verify that you got the field name right.

Re: DateDiff with Direct Query as Measure

luke611 — Fri, 26 Jun 2020 12:10:09 GMT

Thank you Guys for your help. It seems that there is an issue on PBI side when Oracle 11g is being used as a source. PBI has a problem in building proper SQL. I've found two kinds of workarrounds: swith to Import or use own query to get data. Unfortunately both bring a lot of limitation.

I also tried to create measures but actualy I don't know how to solve it using only the measures. I need to calculate avarage of date differences for all rows. So let's say that I have a table with payments, first I need to calculate payment lag and then aggregate lags as an avarage for some dimensions.