Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm having trouble calculating the difference between two date fields in a Direct Query model, I'm looking for the number of hours between them. I've tried three different calculated column formula options and a measure formula and none flag an error in completing the formula, but then when I try to put any of them into a table to view, it just says 'can't display the visual - 'couldn't load the data for this visual':
I've also tried two custom column formulas and both say 'This step results in a query that is not supported in DirectQuery mode.
Unfortunately changing to Import mode is not an option.
These are the different formulas I've tried:
Measure:
Transform data > Add custom column:
What database are you using? I got the measure below to work in a DirectQuery table that uses SQL Server:
Duration Hours = ( MAX ( FactTable[EndDateTime] ) - MAX ( FactTable[StartDateTime] ) ) * 24
Proud to be a Super User!
Hi @DataInsights, sorry I forgot to mention that - it's a PostgreSQL database. Thanks for the suggestion, unfortunately
Duration Hours = ( MAX ( FactTable[EndDateTime] ) - MAX ( FactTable[StartDateTime] ) ) * 24
Also doesn't error when creating it, but then says 'Can't display the visual' when I try and put in a table.
Have you tried using custom SQL in the PostgreSQL connector?
Proud to be a Super User!
Hi @DataInsights, no I haven't, how do I use custom SQL in the PostgreSQL connector?
In Advanced options, enter custom SQL in the "SQL statement" box:
Proud to be a Super User!
User | Count |
---|---|
93 | |
83 | |
77 | |
71 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |