Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
99 | |
80 | |
77 | |
66 |
User | Count |
---|---|
134 | |
106 | |
105 | |
86 | |
72 |