MDX中能否在度量轴中创建命名集？附示例代码

Absolutely! You can definitely create named sets in MDX that incorporate members from the Measures dimension (the Measures axis) — this is a common pattern for dynamically filtering or selecting measures based on query context, just like your sample code attempts to do.

Key Details About Your Approach #

Your code snippet uses nested IIF statements to conditionally include [Measures].[MeasureField] in the [MyFields] set based on the current members of the [School] and [Semester] dimensions. This is a valid use case, but let’s break down a few important points:

1. Tuples vs. Single Members: When you include a measure in a set, you’re often working with tuples (combinations of members from multiple dimensions). For example, when you crossjoin [Student].[UniqueId].members with [MyFields], you’re creating a set of (Student, Measure) tuples — which is perfectly acceptable in MDX.
2. Context-Dynamic Behavior: Your set relies on CurrentMember, meaning its contents will change depending on the query’s slicer (the WHERE clause) or the members on other axes. This is intentional here, but it’s crucial to test how the set behaves across different context scenarios.
3. NonEmpty Filtering: Your [MyNonEmpty] set uses NonEmpty to remove tuples where the measure has no data — this is a smart way to clean up the result set and avoid empty rows/columns.

Example of a Complete Working Query #

Here’s a polished version of your code to illustrate how it might fit into a full MDX query:

WITH 
  SET [MyFields] AS 
    IIF(
      IsEmpty([Measures].[MeasureField]),
      NULL,
      IIF(
        [School].[Key].CurrentMember IS [School].[Key].&[9999] 
        OR [School].[Key].CurrentMember IS [School].[Key].&[8888],
        {[Measures].[MeasureField]},
        IIF(
          [Semester].[Key].CurrentMember IS [Semester].[Key].&[1],
          {[Measures].[MeasureField]},
          NULL
        )
      )
    )
  SET [MyNonEmpty] AS 
    NonEmpty(
      Crossjoin(
        [Student].[UniqueId].[UniqueId].Members,
        [MyFields]
      ),
      [Measures].[MeasureField]
    )
SELECT 
  [MyNonEmpty] ON ROWS,
  {[Measures].[MeasureField]} ON COLUMNS
FROM [YourCube]
WHERE ([School].[Key].&[9999], [Semester].[Key].&[1])

Potential Gotchas to Watch For #

• NULL Handling: If your IIF returns NULL, the set will exclude those tuples entirely — make sure this aligns with your desired behavior.
• Performance: Using CurrentMember and nested IIF in a named set can add overhead if working with large datasets. Consider testing with your cube’s actual size to ensure performance stays acceptable.
• Axis Compatibility: When using the set on an axis, ensure the tuples in the set match the axis’s dimensionality (e.g., a set of (Student, Measure) tuples works on ROWS or COLUMNS, but not on a single-dimension axis unless you adjust the crossjoin).

In short, your approach is valid and a great example of using named sets to dynamically control which measure tuples appear in your query results.

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