Home      Functions     Common Solutions      Contact     

MDX Functions:
MDX Functions

Common Solutions

Common Solutions
Resource Links

MDX Books
MDX Related Software
MDX Related Blogs
Other Links

Contact Us
Microsoft SQL Server Analysis Services Function Page
Function Name: Max
Category: Statistical
Description: The Max function returns the maximum value for a numeric expression within the supplied set.
Syntax: MAX( «Set»[, «Numeric Expression»] )
Technet Link: MDX Max Function

The query below returns the Sale Amount measure and the MaxValue calculated member. The MaxValue calculated member takes the maximum value for members of the Fiscal Year level in the Fiscal hierarchy of the Date dimension.
      MEMBER [Measures].[MaxValue] AS
      MAX([Date].[Fiscal].[Fiscal Year].Members, [Measures].[Sales Amount])
      {[Measures].[Sales Amount],[Measures].[MaxValue]} ON COLUMNS,
      {[Date].[Fiscal].[Fiscal Year].Members} ON ROWS
      [Adventure Works]

The following query uses the Max function to return the value of the Product with the highest List Price for each Product Subcategory.
      MEMBER [Measures].[MaxMember] AS
      HEAD(ORDER([Product].[Product Categories].CurrentMember.Children, [Product].[Product].Properties("List Price"), DESC),1).Item(0).Name
      MEMBER [Measures].[MaxValue] AS
      MAX([Product].[Product Categories].CurrentMember.Children, [Product].[Product].Properties("List Price"))
      {[Measures].[MaxMember], [Measures].[MaxValue]} ON COLUMNS,
      {[Product].[Product Categories].[Subcategory].Members} ON ROWS
      [Adventure Works]


Copyright 2008 by MDXpert.com.
Terms of Use                              Privacy Statement
Site Powered By - WinHost.Com