There are a number of important functions in MDX that return numeric values. Chris Webb, the renowned authority on Multidimensional Expressions and all things related to cubes, explains these in this 30-minute, demo-driven tutorial.

We start with the Count() function, which takes a set and returns the number of items in the set. Even if this function looks similar to a SQL Count(), it cannot be used in an axis deﬁnition—you can only use the Count() function, and other numeric functions, inside a calculated member!

The Sum() function is similar, but it takes not only a set, but also a numeric expression. The numeric expression—usually a tuple—is evaluated for each item in the set, and then all of the values are summed up. Not all values in a cube should be summed up of course. For values such as distinct counts, for example, the Aggregate() function is a better choice. Like the Sum() function, it takes a set and a numeric expression, but unlike Sum() it looks at the AggregateFunction property of the measure values that you are working with, and for the measures that should be summed up, it sums values up, while for distinct counts it will do a distinct count of values over the set, and so on. The Aggregate() function should always be used when you are creating calculated members that return subtotal values. Other numeric values are also available, such as Min(), Max() and Avg().

The CurrentMember function is, generally speaking, the most important function in MDX: understanding how it works is the key to being able to write all kinds of really useful calculations in MDX, including time intelligence calculations. What it does is this: when you call it from inside a calculation, it tells you where the calculation is being executed inside the space of the cube. A calculation is executed inside a cell, and a cell has a tuple that points to it; the CurrentMember function allows you to inspect that tuple, and to ﬁnd which member from any hierarchy on any dimension in the cube is present in that tuple. In most real-world calculations, you use the CurrentMember function to construct tuple or set expressions to ﬁnd values elsewhere in the cube, relative to the cell you are currently in. Make sure to learn about CurrentMember, and the remaining numeric functions by watching Chris explain these concepts in his detailed, live demos, shown in the video.

Log in or purchase access below to the premium version of this content.

- Redeem a prepaid code
- Payment is instant and you will receive a tax invoice straight away.
- Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a

- Introduction (00:08)
- Count() Function (00:31)
- Demo: Using Count (01:26)
- Sum() Function (05:10)
- Demo: Using Sum (05:43)
- Aggregate() Function (11:16)
- Demo: Using Aggregate vs Sum Functions (12:13)
- Avg(), Min(), Max() Functions (14:26)
- Demo: Avg, Min, Max (14:55)
- CURRENTMEMBER Function (16:26)
- What does the Current Member Function Do? (17:22)
- Demo: Current Member (20:58)
- Demo: CURRENTMEMBER with .NAME (21:50)
- Why is CURRENTMEMBER Important? (27:20)
- Summary (32:33)

The Future Series (2019)
Power BI (Current)
Mastering DAX Workshop (Current)
MDX: Multidimensional Expressions
SSAS Cubes
DAX: Data Analysis Expressions (Older)
SQL Server 2012 and SharePoint 2013 BI

- What is Artificial Intelligence?Free
- What is Artificial Stupidity?Free
- The Future of AI—How to Avoid Artificial Stupidity
- The Future of Power BI
- Machine Learning for Security Applications: Why?Free
- Machine Learning for IT Security: From ML to Security AI
- Next Year in Machine Learning, Data Science, AI and BIFree
- Microsoft Machine Learning Technologies: View Towards 2020

- Power BI New Features and Course UpdatesFree
- What is Power BI?Free
- Licensing
- Connecting to Data Sources
- Connecting to Data Sources (2018 Updates)
- Data Import Concepts
- Transforming Data with the Query Editor
- Transforming Data with the Query Editor (2018 Updates)
- Advanced Data Loading
- Modelling Data
- Modelling Data (2018 Updates)
- DAX and the Data Model
- DAX and the Data Model (2018 Updates)
- Power BI Desktop vs Excel
- Cloud and Desktop Power BI Dashboards and Reports
- Power BI Reports (2018 Updates)
- Building Reports in Excel
- Sharing, Distribution, and Security
- Data RefreshFree
- Administration and AuditingFree
- Azure Analysis Services
- Power BI Premium and Power BI EmbeddedFree

Data Mining

- Introduction to Data Mining with Microsoft SQL ServerFree
- Data Mining Concepts and Tools
- Data Mining Model Building, Testing and Predicting with Microsoft SQL Server and Excel
- What Are Decision Trees?Free
- Decision Trees in Depth
- Why Cluster and Segment Data?Free
- Clustering in Depth
- What is Market Basket Analysis?Free
- Association Rules in Depth
- HappyCars Sample Data Set for Learning Data Mining
- Code and Data Samples (R, R Services, SSAS)Free

- MDX Basic ConceptsFree
- MDX QueriesFree
- MDX Calculated Members
- MDX: Member and Set Functions
- What is the MDX Current Member Function?Free
- MDX Numeric Functions and CURRENTMEMBER
- MDX: Previous Period Growths, Shares & Moving Averages
- More MDX Member & Set Functions
- What is a Subselect in MDX?Free
- Exists, MDX Solve Order, and Subselects
- MDX Performance Diagnosis with Profiler

Data Science with AML Classic (Course Retiring in 2022)

Excel BI for Enterprises

Power BI in Excel 2013

Power BI 2017 (Course Retiring in 2020)

- What is Power BI?Free
- Connecting to Data Sources
- Data Import Concepts
- Transforming Data with the Query Editor
- Advanced Data Loading
- Modelling Data
- DAX and the Data Model
- Power BI Desktop vs Excel
- Cloud and Desktop Power BI Dashboards and Reports
- Building Reports in Excel
- Data RefreshFree
- Administration and AuditingFree

- DAX in Action!Free
- DAX: Calculated Columns vs. MeasuresFree
- Introduction to DAX in Excel
- DAX Evaluation Context
- DAX: The CALCULATE Function
- Querying with DAX
- What is Time Intelligence?Free
- DAX Time Intelligence
- Many-to-many Relationships in DAXFree
- DAX Patterns: Banding, New vs Old, Many-to-many
- How to Calculate with Hierarchies in DAX?Free
- Hierarchies in DAX
- DAX AW Sample Data Set

- Microsoft Business Analytics with Office 2013, SharePoint 2013 and SQL Server 2012
- Introduction to SQL Server 2012 Business Intelligence (Video)
- Introduction to PowerPivot for SQL Server 2012
- Introduction to Power View in SQL Server 2012 Reporting Services
- Introduction to BI Semantic Model & SQL Server 2012 Analysis Services

## Comments

russella0 · 10 December 2018

Hi Chris,

At time code 22:19 you create a calculated measure called DEMO that returns [DATE].[CALENDAR YEAR].[2001] that has no mention of another measure. I see this works and I understand what it's doing. What I don't understand is why this doesn't cause infinite recursion as we had earlier in the course when you create a calculated measure that doesn't move the context of the query to another measure.

Thanks,

Andy

Chris Webb · 10 December 2018

Hi Andy, I think the time you're referencing is wrong, but I think I know what you're talking about. DEMO is not a calculated measure - it's a calculated member on the Calendar Year hierarchy of the Date dimension. You only get infinite recursion on measures when you don't reference a measure in the calculation; in this case, when I create a calculated member on the Calendar Year hierarchy of the Date dimension, I don't get infinite recursion because I'm referencing another member on this hierarchy.

russella0 · 10 December 2018

Hi Chris,

Thank you for coming back to me.

The MDX that's on the screen at 22:34 is

WITH MEMBER MEASURES.DEMO

AS

[DATE].[CALENDAR YEAR].CURRENTMEMBER.NAME

So surely that is a measure ?

I appreciate this was 4 years ago for you now and that it's a bit above and beyond for me to expect direct support, it's more that MDX is finally opening up to me and I just can't get the penny to drop on this one :(

Best,

Andy

Chris Webb · 11 December 2018

Ahh yes, I understand what you're asking now. In this case there is no infinite recursion because the calculation just returns the name of the current member on the Calendar Year hierarchy. In the previous example the calculation uses a tuple to get a value from the cube, and when you are constructing a tuple to get data that's when you run the risk of infinite recursion because your tuple might refer to the cell you're currently calculating if you're not careful.

russella0 · 11 December 2018

Ah, yes, that makes sense now. Thank you for coming back to me.