SSAS Tabular/OLAP: Excel Pivot / Calculation group and filter bugs

Question

Hi,

• SSAS Tabular on SQL Server 2019 Enterprise, Comp level 1500.
• Excel 365, Version 2105

When I create calculation groups with more than TWO items, Excel has a strange behaviour. I've tested the same Calc Groups with Power BI and they work as they should.
It doesn't matter if the calculation items actually do anything or I just leave them with = SELECTEDMEASURE() for testing purposes.

Excel:

First bug:

• When I have several filters on my pivot and put the calculation group items on columns (or rows, it doesn't matter) everything works fine.
• When I want to filter the calculation items (e.g. just select 3 of them) Excel internally loses all filters (I can trace that on SQL Server) and I have to press ESC
• When I refresh the pivot just after ESC and apply the calculation group filteres again, they are filtered correctly.
• I've tested this with several users, everywhere the same problem.

Second bug (pretty sure it's connected):

• We also tested just basic filtering with an OLAP (MDX) Cube and with the newer Excel Versions (maybe starting from 2103?)
• If I drag a column from Filters to Rows, the filter is lost. But it's not always, just sometimes.
• With older excel versions we didn't have this problem.

Is anyone else experiencing those problems?

Answer 1

Hi Alexei,

I have good news. Updating SQL Server 2019 to CU11 (latest cumulative update) solves the problem, everything works as expected now.

I suppose it's this "fix" from the list in CU11:

https://support.microsoft.com/en-us/topic/kb5003249-cumulative-update-11-for-sql-server-2019-657b2977-a0f1-4e1f-8b93-8c2ca8b6bef5#bkmk_14070005

14070005 - "Fixes the incorrect results that occur in SSAS 2019 tabular mode when a DAX queries a calculated measure that depends on calc group Calculation item"

So, Excel isn't the problem.

Thanks anyway :)

BR
Lukas

Answer 2

These seems to be Excel issues, not SSAS.
For what it's worth, I've been seeing intermittent loss of filter data when moving an attribute from filters to rows for years now. Always thought of it as Excel's quirk, not bug. Could've been wrong there.