Direct Query Function not allowed

[MrDannyHarry]

Hi All,

I have a DAX expression which I am using on a new measure.

This worked  previosuly on data imported but now using direct query I have the following error.

Function 'HOUR' is not allowed as part of calculated column DAX expressions on DirectQuery models

My Expression is the following:

TotalTalk Time = var total = sum('database_single   public   mb_callstats'[call_servicing_sec]) var hours = int(total/3600) var minutes = int(mod(total, 3600)/60) var seconds = mod(mod(total, 3600),60) return hours & " hours " & minutes & " minutes " & seconds & " seconds" 

[Anonymous]

Hi @MrDannyHarry 

I build a table in SQL to have a test by your dax expression.

Result:

TotalTalk Time is calculated column and TotalTalk Time2 is measure. I can get the correct result both of two.

Here I have some advice.

Please check the version of your Power Bi Desktop.

The latest version:

If your version is not the latest one, you may update and try again.

Click this link to download : Download

If this reply still couldn't help you solve your problem, please show me more details about your issue. You may provide me the screenshot of your issue, the dax tou use and the error it shows. And you may show me your data model as well.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[MrDannyHarry]

@Anonymous 

Thanks for your help!

I have the latest version, I have copied your code and still getting the same error.

You can see the measure below.

The code in this measure is the same as you supplied.

MY DB Tables show this data

I then use the following command in a custom column to convert to seconds

call_servicing_sec = SECOND('database_single   public   mb_callstats'[call_servicing]) + MINUTE('database_single   public   mb_callstats'[call_servicing]) * 60 + HOUR('database_single   public   mb_callstats'[call_servicing]) * 3600

The only differnce is I am using a third party ODBC DirectQuery connector for Postgres.

I couldn't use the main Postgres connector as it had a certificate error. Could this be the issue?

[MrDannyHarry]

I have got past this by getting Postgres Direct Query to work  instead of ODBC Direct Query.

I am getting a Folding error now when trying to add visuals with calculations.

[Anonymous]

Hi @MrDannyHarry 

Could you show me more details? Such as your issue Screenshot, the calculations you build, the visual you want to build and the underlying data sample you use.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@MrDannyHarry , I checked on the Direct query file. Hours is supported on a column

I used in a direct query -

 hour(Sales[Sales Date])

[parry2k]

@MrDannyHarry are you creating a measure or a column? I don't see HOUR function used in the expression you shared, little bit lost in the question and expression you shared.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡