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

[Charles-C]

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)

[bcdobbs]

Hi,

COALESCE was added to DAX in 2020. (The COALESCE function in DAX - SQLBI) 

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]

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]

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-mo...

if you're looking at max supported levels.

[Charles-C]

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.