MDX (Multidimensional Expressions) Cheat Sheet

Updated 2026-05-26

🧠Study flashcards on this topic143 cards · spaced repetition→

MDX (Multidimensional Expressions) is a query language for OLAP (Online Analytical Processing) databases, primarily used with Microsoft SQL Server Analysis Services (SSAS) and other multidimensional database systems. Unlike SQL's flat relational model, MDX navigates hierarchical cube structures with dimensions, measures, and complex aggregations. Its syntax resembles spreadsheet formulas and enables sophisticated time-based calculations, drill-down operations, and dynamic filtering across multiple dimensionsmaking it essential for business intelligence reporting and data analytics where users need to slice, dice, and pivot multidimensional data rapidly.

What This Cheat Sheet Covers

This topic spans 20 focused tables and 168 indexed concepts, 143 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Query Structure BasicsTable 2: Members, Tuples, and SetsTable 3: Calculated MembersTable 4: Named SetsTable 5: Dimension NavigationTable 6: Hierarchy and Drill OperationsTable 7: Set OperationsTable 8: Filtering and OrderingTable 9: Time Intelligence FunctionsTable 10: Aggregate FunctionsTable 11: Statistical and Regression FunctionsTable 12: Conditional and Logical FunctionsTable 13: Scope and Assignment StatementsTable 14: KPI FunctionsTable 15: Cell PropertiesTable 16: String and Conversion FunctionsTable 17: Member Properties and AttributesTable 18: Advanced Set FunctionsTable 19: Query Performance OptimizationTable 20: Miscellaneous Utility Functions

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: Query Structure Basics

Core MDX queries are built from SELECT, FROM, WHERE, and WITH clauses, each playing a distinct role; understanding how axes, slicer, and subselects interact is the foundation before writing any calculation.

Component	Example	Description
SELECT Statement	`SELECT [Measures].[Sales] ON COLUMNS,` `[Product].[Category].Members ON ROWS` `FROM [SalesCube]`	• Core query syntax specifying what to retrieve (axes), from which cube, and optional filtering • axes define result dimensionsCOLUMNS for column headers, ROWS for row headers
FROM Clause	`FROM [SalesCube]`	• Specifies the target cube or subcube to query • can reference a physical cube or a subselect expression that restricts cube space
WHERE Clause (Slicer Axis)	`WHERE ([Time].[2025], [Geography].[USA])`	• Filters the entire result set by specifying a tuple forming the slicer axis • reduces cube dimensions globallyoften called the filter axis
WITH Clause	`WITH MEMBER [Measures].[Profit] AS` `[Measures].[Sales] - [Measures].[Cost]` `SELECT ...`	• Defines query-scoped calculated members or named sets before the SELECT statement • members/sets only exist within this query, not persisted to cube
NON EMPTY	`SELECT NON EMPTY [Product].Members ON ROWS`	• Excludes members or tuples with no data (all NULL/empty measures) • significantly improves performance by reducing result set size
Axis Specification	`{[Product].[Bikes], [Product].[Clothing]}` `ON COLUMNS`	• Defines a set of members to display on a specific axis • COLUMNS (Axis 0), ROWS (Axis 1), PAGES (Axis 2), SECTIONS (Axis 3), CHAPTERS (Axis 4)
Subselect (Nested SELECT)	`SELECT [Measures].[Sales] ON COLUMNS` `FROM (` `SELECT [Time].[2025].Children ON COLUMNS` `FROM [SalesCube])` `WHERE [Geography].[USA]`	• Nested SELECT in FROM clause that restricts cube space before outer query evaluation • changes hierarchy structure and filters differently than WHERE clause
HAVING Clause	`NON EMPTY [Product].Members` `HAVING [Measures].[Sales] > 1000` `ON ROWS`	• Filters axis contents after NON EMPTY is applied • alternative to FILTERcleaner syntax for axis-level conditions, evaluates in axis scope

Table 2: Members, Tuples, and Sets

Members, tuples, and sets are the three fundamental building blocks of all MDX expressions; every function, axis, and filter is ultimately composed of these primitives.

Concept	Example	Description
Member	`[Time].[Year].[2025]`	• Single position in a dimension hierarchy • atomic unit of MDX datarepresents a unique point along a dimension

The rest of this section is part of the full cheat sheet

Table 3: Calculated Members

Calculated members let you define custom metrics on-the-fly or at session scope; understanding scope, solve order, and visual properties is key to writing correct and maintainable calculations.

Technique	Example	Description
WITH MEMBER (Query-Scoped)	`WITH MEMBER [Measures].[Profit] AS` `[Measures].[Sales] - [Measures].[Cost]` `SELECT [Measures].[Profit] ON COLUMNS`	• Defines a calculated member within a single query • most common approach for ad-hoc calculationsexists only during query execution

The rest of this section is part of the full cheat sheet

Table 4: Named Sets

Named sets give reusable, readable names to frequently referenced member collections; understanding query-scoped vs session-scoped, and dynamic vs static evaluation, prevents subtle context bugs.

Technique	Example	Description
WITH SET (Query-Scoped)	`WITH SET [TopProducts] AS` `TOPCOUNT([Product].Members, 10,` `[Measures].[Sales])` `SELECT [TopProducts] ON ROWS`	• Defines a reusable set expression within a query • eliminates repetition and improves readabilityset only exists during this query

The rest of this section is part of the full cheat sheet

Table 1: Query Structure Basics

Component	Example	Description
SELECT Statement	`SELECT [Measures].[Sales] ON COLUMNS,` `[Product].[Category].Members ON ROWS` `FROM [SalesCube]`	• Core query syntax specifying what to retrieve (axes), from which cube, and optional filtering • axes define result dimensionsCOLUMNS for column headers, ROWS for row headers
FROM Clause	`FROM [SalesCube]`	• Specifies the target cube or subcube to query • can reference a physical cube or a subselect expression that restricts cube space
WHERE Clause (Slicer Axis)	`WHERE ([Time].[2025], [Geography].[USA])`	• Filters the entire result set by specifying a tuple forming the slicer axis • reduces cube dimensions globallyoften called the filter axis
WITH Clause	`WITH MEMBER [Measures].[Profit] AS` `[Measures].[Sales] - [Measures].[Cost]` `SELECT ...`	• Defines query-scoped calculated members or named sets before the SELECT statement • members/sets only exist within this query, not persisted to cube
NON EMPTY	`SELECT NON EMPTY [Product].Members ON ROWS`	• Excludes members or tuples with no data (all NULL/empty measures) • significantly improves performance by reducing result set size
Axis Specification	`{[Product].[Bikes], [Product].[Clothing]}` `ON COLUMNS`	• Defines a set of members to display on a specific axis • COLUMNS (Axis 0), ROWS (Axis 1), PAGES (Axis 2), SECTIONS (Axis 3), CHAPTERS (Axis 4)
Subselect (Nested SELECT)	`SELECT [Measures].[Sales] ON COLUMNS` `FROM (` `SELECT [Time].[2025].Children ON COLUMNS` `FROM [SalesCube])` `WHERE [Geography].[USA]`	• Nested SELECT in FROM clause that restricts cube space before outer query evaluation • changes hierarchy structure and filters differently than WHERE clause
HAVING Clause	`NON EMPTY [Product].Members` `HAVING [Measures].[Sales] > 1000` `ON ROWS`	• Filters axis contents after NON EMPTY is applied • alternative to FILTERcleaner syntax for axis-level conditions, evaluates in axis scope

Table 2: Members, Tuples, and Sets

Members, tuples, and sets are the three fundamental building blocks of all MDX expressions; every function, axis, and filter is ultimately composed of these primitives.

Concept	Example	Description
Member	`[Time].[Year].[2025]`	• Single position in a dimension hierarchy • atomic unit of MDX datarepresents a unique point along a dimension

Table 3: Calculated Members

Calculated members let you define custom metrics on-the-fly or at session scope; understanding scope, solve order, and visual properties is key to writing correct and maintainable calculations.

Technique	Example	Description
WITH MEMBER (Query-Scoped)	`WITH MEMBER [Measures].[Profit] AS` `[Measures].[Sales] - [Measures].[Cost]` `SELECT [Measures].[Profit] ON COLUMNS`	• Defines a calculated member within a single query • most common approach for ad-hoc calculationsexists only during query execution

Table 4: Named Sets

Named sets give reusable, readable names to frequently referenced member collections; understanding query-scoped vs session-scoped, and dynamic vs static evaluation, prevents subtle context bugs.

Technique	Example	Description
WITH SET (Query-Scoped)	`WITH SET [TopProducts] AS` `TOPCOUNT([Product].Members, 10,` `[Measures].[Sales])` `SELECT [TopProducts] ON ROWS`	• Defines a reusable set expression within a query • eliminates repetition and improves readabilityset only exists during this query