The ISEMPTY function in #dax #powerpivot #tabular

Microsoft silently added the ISEMPTY function to the DAX language in Analysis Services build 11.00.3368 (SQL Server 2012 SP1 CU4). This function is particularly important in DAXMD (when you use DAX to query a Multidimensional model), because produces a much better execution plan in OLAP than the alternatives based on COUNTROWS.

There is an advantage in using it in Tabular/Power Pivot models, too, even if there is an issue using it in Power Pivot. You can upgrade Power Pivot on Excel 2010 (you can download the new version of Power Pivot for Excel 2010 as part of the cumulative update released for SQL Server), but you cannot upgrade Excel 2013. This is done only through Office updates and up to now such a feature has not been added to the released versions of Excel 2013 (at least until version 15.0.4605.1003). The funny thing is that, if you have Power Pivot for SharePoint, you can have a server that would be able to use new features (such as ISEMPTY function) but you are not able to create an Excel 2013 file using them!

Last week I wrote a an article on SQLBI that describes the available syntaxes you can use to check empty table condition in DAX. You will find a few code examples there. I hope that Microsoft will soon release an upgrade in Power Pivot for Excel 2013, too.