SummarizeColumns() and AddMissingItems() may not be used in this context (only on v.16.0.70.21 )

[dbeavon3]

I realize that it is unusual but I still use the SSAS extensions in Visual Studio for building models.  I deploy with XMLA endpoints.

(I have heard that lots of other folks prefer the Power BI desktop or the Tabular Editor to build models, but I can't imagine why. 😉

The designer version in Visual Studio is
Microsoft SQL Server Analysis Services Designer
Version 16.0.20583.0

According to SSMS this launches a version of tabular SSAS v.16.0.70.21

To make a long story short, when I build certain calculations in this version I get an error message from the OLAP service:

Error: Calculation error in measure 'Values'[x]: SummarizeColumns() and AddMissingItems() may not be used in this context.

... however if I publish the model to the server, v.17.0.40.18 compatibility 1700

.. then the same calculation works fine.  Here is an example of a calc that doesn't work properly when using the SSAS designer in Visual Studio:

VAR SelectedFiscalYear = SELECTEDVALUE('Time'[Fiscal Year Value], 0)
VAR SelectedFiscalQuarter = SELECTEDVALUE('Time'[Fiscal Quarter Value], 0)
VAR SelectedFiscalMonth = SELECTEDVALUE('Time'[Fiscal Month Value], 0)
VAR SelectedFiscalWeek = SELECTEDVALUE('Time'[Fiscal Week Value], 0)
   
VAR FiscalDayValueTable = CALCULATETABLE(SUMMARIZECOLUMNS('Time'[Fiscal Day Value],  "CountFiscalDayRows", COUNTROWS('Hidden Inventory Balance')), 

    REMOVEFILTERS('Product'), 
    REMOVEFILTERS('Location Code'),
    REMOVEFILTERS('Location Type'),
    REMOVEFILTERS('Branch'))


    
VAR MaxFiscalDayValue = MAXX(FiscalDayValueTable, [Fiscal Day Value])

RETURN IF(ISBLANK(COUNTROWS('Hidden Inventory Balance')),  BLANK(),   

IF(SelectedFiscalYear > 0 && SelectedFiscalWeek > 0,
    CALCULATE(SUM('Hidden Inventory Balance'[CorpCostTotal]),
        FILTER(ALL('Time'), 'Time'[Fiscal Year Value] = SelectedFiscalYear && 'Time'[Fiscal Week Value] = SelectedFiscalWeek && 'Time'[Fiscal Day Value] = MaxFiscalDayValue)),

    IF(SelectedFiscalYear > 0 && SelectedFiscalMonth > 0,
        CALCULATE(SUM('Hidden Inventory Balance'[CorpCostTotal]),
            FILTER(ALL('Time'), 'Time'[Fiscal Year Value] = SelectedFiscalYear && 'Time'[Fiscal Month Value] = SelectedFiscalMonth && 'Time'[Fiscal Day Value] = MaxFiscalDayValue)), 

        IF(SelectedFiscalYear > 0 && SelectedFiscalQuarter > 0,
            CALCULATE(SUM('Hidden Inventory Balance'[CorpCostTotal]),
                FILTER(ALL('Time'), 'Time'[Fiscal Year Value] = SelectedFiscalYear && 'Time'[Fiscal Quarter Value] = SelectedFiscalQuarter && 'Time'[Fiscal Day Value] = MaxFiscalDayValue)),
                    
            IF(SelectedFiscalYear > 0,
                CALCULATE(SUM('Hidden Inventory Balance'[CorpCostTotal]),
                    FILTER(ALL('Time'), 'Time'[Fiscal Year Value] = SelectedFiscalYear && 'Time'[Fiscal Day Value] = MaxFiscalDayValue)), 

                0 ) ) ) ) ) 

I wonder if I'm going to have to abandon visual studio for modern tabular models.  Does anyone know if there was a change to "fix" SUMMARIZECOLUMNS in measures between v.16.0.70.21 and v.17.0.40.18?

I think there were past blogs saying not to use SUMMARIZECOLUMNS in measures, but apparently that doesn't apply to new versions.  Any info would be appreciated.

[GilbertQ · Accepted solution]

Hi @dbeavon3 

Yes, you are correct. The DLLS that you are using are very old and there have been changes to DAX and subsequent releases and that is why it works in the service but not on your local instance. So your option is either to try and find a way to update the DLLS to the latest version or to use tools which use the latest version so that your code will work as expected.

[dbeavon3]

OK, that is good to know.  I think this may finally be the tipping point when I have to start using the crappy PBI desktop.  I think it will be better once PBI projects are GA, but I've been waiting for that to happen for years now.  I don't think developer tools get much better than what is built in VS, but that is probably just a grey-beard opinion.

According to the docs, the newer SSAS tabular model functionality isn't available.  Looks like we are stuck in time at SQL 2016.

https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver17...

Do you happen to know if there is a blog that says when the behavior of SUMMARIZECOLUMNS was fixed?
The blog still makes it sound like we shouldn't be using this in measures:
Introducing SUMMARIZECOLUMNS - SQLBI

I'm only using it minimally, and only because it performs quite well for the purpose of calculating non-empty behavior on ending balances.