Forum Discussion
MdxScript(Model) (4, 60) Failed to resolve name 'COALESCE'
Hi bcdobbs,
Thank you for the feedback. Yes, I am aware that that COALESCE was only introduced in 2020 and the reason I was posting this question was becuase we have a similar model that is deployed also with the SQL Server 2016 RTM (1200) – Compatibility Level to an Azure analysis service and through that the COALESCE seems to be working fine.
Could it be that the Azure analysis services uses its own formula engine, sorry not too strong on Azure yet.
Community ChampionThat does sound odd. It's possible that the compatibility level simply describes the structure of TOM rather than specific DAX supported (Not too sure!)
If you connect to your azure model with tabular editor does it still show 1200?
There is a note of caution at bottom of
if you're looking at max supported levels.
I do have to apologise, I had the compatibility level incorrect, I confirmed now on both the Azure and local instance and both are using the SQL Server 2017 / Azure Analysis Services (1400) compatibility level. Even though I initially mentioned the incorrect compatibility the issue is still the same on the local instance
To answer your question, Yes the Tabular editor and the database both shows the SQL Server 2017 / Azure Analysis Services (1400) compatibility level.
Images are from the the model and server where the COALESCE is working.
- bcdobbs
No worries. I'll have a play and see if I can recreate my end.
I suspect your conclusion is correct though. The underlying formula engine for Azure AS supports the new DAX as it's constantly updated while your on prem instance is stuck with what it has.