MdxScript(Model) (4, 60) Failed to resolve name 'COALESCE'

Question

Good Day.I have an SSAS tabular model (SQL Server 2016 RTM (1200) – Compatibility Level) that I use to connect to Power BI Desktop. All my measures are created in the tabular model so that I can use the same model for different reports. I'm not keen on (blank) results in a visual so I make use of COALESCE to resolve this.When I deploy the model with the measure then I get an error: "Failed to resolve name 'COALESCE'. It is not a valid table, variable, or function name." and it won’t deploy. If I deploy the model without the measure then it deploys fine, I can then add the measure afterwards and it will then deploy successfully again but this time with my measure but when I try and use the measure in Power BI I get the same error.Whilst connected to my SSAS model, if I try and create a new measure in Power BI then the COALESCE function is not available in Power BI.Has anyone experienced this before or can anyone perhaps give me some guidance on where I could be going wrong?I am running Power BI desktop version 2.100.1182.0 64-bit (December 2021)&nbsp;

bcdobbs · Answer

Hi,COALESCE was added to DAX in 2020. (The COALESCE function in DAX - SQLBI)&nbsp;&nbsp;You're connecting to a 2016 SSAS instance and so it isn't supported. When you make a connection like that you are using the formula engine that belongs to the SSAS instance and not power bi.

charles-c · Answer

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.

bcdobbs · Answer

That 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

https://docs.microsoft.com/en-us/analysis-services/tabular-models/compatibility-level-for-tabular-models-in-analysis-services?view=asallproducts-allversions#check-supported-compatibility-level-for-a-server-in-ssms

if you're looking at max supported levels.

charles-c · Answer

I do have to apologise, I had the compatibility&nbsp;level incorrect, I confirmed now on both the Azure and local instance and both are using the&nbsp;SQL Server 2017 / Azure Analysis Services (1400)&nbsp;compatibility&nbsp;level. Even though I initially mentioned the&nbsp;incorrect&nbsp;compatibility the issue is still the same on the local instance&nbsp;To answer your question, Yes the Tabular editor and the database both shows the&nbsp;SQL Server 2017 / Azure Analysis Services (1400)&nbsp;compatibility&nbsp;level.&nbsp;Images are from the the model and server where the COALESCE is working.&nbsp;

bcdobbs · Answer

No worries. I'll have a play and see if I can recreate my end.&nbsp;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.

Forum Discussion

MdxScript(Model) (4, 60) Failed to resolve name 'COALESCE'

5 Replies

Related content

MDXScript(Model) calculation Error

MdxScript(Model)(17,3) calculation error

MdxScript(Model) Calculation error in measure

Error MdxScript(Model) (196,47)

Mdxscript (Model) Calculation error