SSAS Tabular grand total wrong with hierarchy filter

Question

I am using SSAS Tabular 2022 and has model as below:

Model

The total amount is calculated as

Total Amount:= sum(Amount[Amount])

When I view in Excel and filter the model by the Year-Month-Date, the first time under any level of the filter is always correct. For example, I filter first on date 2024年05月04日:

Filter on date 2024年05月04日

Result on date 2024年05月04日

But then when I filter other date in May or May itself (one level up), the grand total does not change and remains what I selected previously. For example, I filter on month 2024年05月01日:

Filter on month 2024年05月01日

Result on month 2024年05月01日

I tried refreshing data in data tab but the results are still the same. Why is that? And how should I fix it?

Edit:

After further digging, I found out the problem seems to happen to the tabular cube itself when using MDX to query. I got the below two MDXs from SQL Server Profiler when the filters are applied in the Excel Pivot table.

Filter to date (2024年06月05日):

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Calendar].[Date].[All]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Model] WHERE ([Calendar].[Year - Month - Date].[Date].&[2024年06月05日T00:00:00],[Measures].[Total Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Filter to month (2024年06月01日):

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Calendar].[Date].[All]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Model] WHERE ([Calendar].[Year - Month - Date].[Month].&[2024年06月01日T00:00:00],[Measures].[Total Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

By running in SSMS, the result is shown below: All and 6/5/2024 have the same value: MDX query cube: month after date

After I clear the cache using XMLA below:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <Object>
 <DatabaseID>HierarchyFilterTest</DatabaseID>
 </Object>
</ClearCache>

MDX query cube: after clearing cache

The result becomes correct. But the problem is I cannot clear the cache in Excel. Anyone have idea of how to do it in Excel? Or is there other way to fix it?

Answer 1

What I understood from what you shared, in your calendar table the month level is actually a date and your hierarchy is year/month/date and that doesn't filter the whole month it filters only the single value of the date and because you have date on rows, Excel queries all the dates and the Pivot cache ends up showing the previous grand total.

You need to create a month column for the month level instead of the date and sort it by the YearMonthNumber :

YearMonthText = FORMAT([Date], "yyyy/MM")
YearMonthNumber = YEAR([Date] * 100 + MONTH[Date]

and build your hierarchy Year + YearMonthText + Date

• Vincent: Thank you so much for your reply! Unfortunately, the problem still exists after creating a separate month column. It seems that the data is cached and cannot be refreshed by Excel in the tabular cube.

Answer 2

I found out the issues seems to be because I am using the same column as both the hierarchy and dimension. If I explicitly define another column for the hierarchy, like below. The problem goes away.

Model

The DateHierarchy is defined as:

DateHierarchy := 'Calendar'[Date]