topic Re: Calculated measures not working in Dashboards for queries with big result in Data Engineering

Calculated measures not working in Dashboards for queries with big result

797646 — Fri, 21 Feb 2025 10:26:05 GMT

Queries with big result are executed on cluster.
If we specify calculated measure as something like
cal1 as
count(*) / count(distinct field1)

it will wrap it in backticks as

`count(*) / count(distinct field1) ` as `cal1`

functions are not identified inside backticks and it fails with UNRESOLVED_COLUMN exception.
It works fine if the data is small then query is executed in browser and there is no templating i guess then.

can we please look into it or suggest the way around.

Re: Calculated measures not working in Dashboards for queries with big result

Brahmareddy — Fri, 21 Feb 2025 20:47:45 GMT

Hi @797646

How are you doing today? As per my understanding, It looks like Databricks automatically wraps your calculated measure in backticks, causing function resolution issues when executing large queries on a cluster. Since it works fine for small datasets executed in the browser, the issue might be related to templating. To work around this, try explicitly casting your expression by multiplying by 1.0 (e.g., count(*) * 1.0 / count(distinct field1) AS cal1), use a CTE to define counts separately before applying the calculation, or restructure the query to define cal1 as a derived column. If using a templated query interface, try executing it in a SQL notebook instead to check if the behavior changes. Let me know if this helps!

Good day.

Regards,

Brahma

Re: Calculated measures not working in Dashboards for queries with big result

797646 — Sat, 22 Feb 2025 06:05:11 GMT

Hi @Brahmareddy
This didn't work
count(*) * 1.0 / count(distinct field1) AS cal1)

gave me same error.

But as per this feature release
https://docs.databricks.com/aws/en/dashboards/datasets/calculated-measures

this should work out of box, otherwise it's not much useful.
Can we check if there is an issue with this feature.

I can try to make it work by using derived columns through CTE.
but just curious if the feature released supports it.

Re: Calculated measures not working in Dashboards for queries with big result

AdamKleiman — Tue, 11 Mar 2025 16:37:52 GMT

Thanks for the tip - i tried multiplying by 1.0 but I believe it's a namespace error.

The error message indicates "A column with name `count(*) / count(distinct field1)` is not found."

After some testing, this only occurs when the distinct keyword is used.

Re: Calculated measures not working in Dashboards for queries with big result

Slav — Mon, 17 Mar 2025 07:10:58 GMT

I have the same issue.
calculated measures with "count(distinct x)" in them will not work!!

Re: Calculated measures not working in Dashboards for queries with big result

Alberto_Umana — Mon, 17 Mar 2025 16:50:05 GMT

Hello Team,

Could you all try with all caps? COUNT(DISTINCT xxx)