Calculated measures not working in Dashboards for queries with big result

[797646]

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. 

[Brahmareddy]

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

[AdamKleiman]

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. 

[797646]

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. 

[Slav]

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

[Alberto_Umana · Accepted solution]

Hello Team,

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