MDX中需新增度量统计单元格事实数量吗？能否通过查询实现？

Great question! You don’t need to create a dedicated [Count of Facts] measure to get the number of fact rows associated with each ThingType—you can calculate this directly in your MDX query, just like how you’d use COUNT(*) in SQL. Here’s how to do it with your existing schema:

Method 1: Use COUNT with EXISTING to Target Fact Rows #

The key is to use the EXISTING keyword to restrict the count to only the fact rows that fall within the current context (in your case, each ThingType on the rows). You’ll reference the primary key of your fact table to count distinct rows:

SELECT NON EMPTY { 
    [Measures].[Turnips],
    // Calculate fact row count on the fly
    COUNT(EXISTING [FactFacts].[FactID].MEMBERS) AS [Count of Facts]
} ON COLUMNS,
NON EMPTY { ([ThingType].[ThingTypeID].MEMBERS) } ON ROWS
FROM [Things]

Why this works: #

• EXISTING tells MDX to only consider the FactID members that are relevant to the current ThingType in the row context.
• Counting the FactID members ensures you get an accurate count of individual fact rows, just like COUNT(*) in SQL.

Method 2: Use the Built-in Row Count Measure (if using SSAS) #

If you’re working with SQL Server Analysis Services (SSAS), your measure group might already have a hidden [Row Count] measure that tracks the number of rows in the fact table. You can use this directly in your query without creating a new measure:

SELECT NON EMPTY { 
    [Measures].[Turnips],
    [Measures].[Row Count]
} ON COLUMNS,
NON EMPTY { ([ThingType].[ThingTypeID].MEMBERS) } ON ROWS
FROM [Things]

Note: #

If the [Row Count] measure isn’t visible, you might need to enable "Show Hidden Measures" in your MDX query tool (like SQL Server Management Studio) to access it.

Why your initial attempt might have failed: #

Chances are you didn’t use the EXISTING keyword to narrow down the fact rows to the current context. Without it, MDX would count all FactID members in the entire cube, not just those tied to the specific ThingType you’re grouping by.

内容的提问来源于stack exchange，提问作者Richard Barraclough