求助：MDX查询无法获取按产品和年份统计的最高销售额员工

Let's break down what's going wrong with your current query and fix it step by step:

Issues in Your Original Query #

1. Spelling typos: Maximun should be Maximum, and EmpLoyee has an inconsistent capitalization (small detail but can cause confusion or syntax hiccups).
2. Context mismatch: Your TOPCOUNT and MAX functions are using a full cross product of products and employees, without respecting the current year and product context from your row axis. That means the query isn't calculating the top employee for each specific product-year combination—it's trying to compute a global value, which breaks MDX's context rules and leads to errors.
3. Potential hierarchy misreference: [Employee].[Geography].[Employee] looks unusual. Typically, employee dimensions use a hierarchy like [Employee].[Employee].[Employee] (where the middle level is the employee name/key), and Geography is an attribute of the employee, not a parent hierarchy. If your employee dimension is structured differently, adjust the path, but I'll assume this was a minor mislabel for now.

Corrected MDX Query #

WITH 
-- Calculate the maximum sales amount for the current product-year combination
MEMBER measures.[Maximum Sales] AS 
    MAX(
        -- Get only employees who have sales for the current product and year
        EXISTS(
            [Employee].[Employee].[Employee].MEMBERS,
            ([Product].[ProductName].CurrentMember, [Order Date].[Calendar].CurrentMember)
        ), 
        [Measures].[Sales Amount]
    )
-- Get the name of the employee with the highest sales for the current product-year
MEMBER measures.[Top Employee] AS 
    HEAD(
        -- Order relevant employees by sales descending, then take the first result
        ORDER(
            EXISTS(
                [Employee].[Employee].[Employee].MEMBERS,
                ([Product].[ProductName].CurrentMember, [Order Date].[Calendar].CurrentMember)
            ),
            [Measures].[Sales Amount],
            BDESC
        ),
        1
    ).ITEM(0).NAME
SELECT 
    { measures.[Maximum Sales], measures.[Top Employee] } ON COLUMNS,
    ([Product].[ProductName].[ProductName], [Order Date].[Calendar].[Year]) ON ROWS
FROM [Sales]

Key Fixes Explained #

• EXISTS function: This filters the employee set to only those who have actual sales data for the current product and year (from the row axis). This ensures we're only considering relevant employees for each row, not the entire employee list.
• Context-aware calculations: Using CurrentMember for product and year ties the calculation directly to the row we're evaluating, so each product-year pair gets its own unique top employee and max sales value.
• ORDER + HEAD instead of TOPCOUNT: While TOPCOUNT works in some scenarios, combining ORDER (to sort by sales descending) and HEAD (to take the first result) is more explicit in MDX calculated members and avoids context-related bugs.
• Hierarchy adjustment: If your employee hierarchy is indeed [Employee].[Geography].[Employee], just replace [Employee].[Employee].[Employee].MEMBERS with that path in both EXISTS blocks to match your cube's structure.

Edge Case Note #

If multiple employees have the same maximum sales for a product-year pair, this query will return the first one in the ordered list. If you want to handle ties (e.g., return all top employees), you'd need to adjust the calculation to concatenate names, but that's a more advanced scenario.

内容的提问来源于stack exchange，提问作者Vanesa García