MDX Calculations Part 4 – COUNT Aggregation Function

MDX Numeric Aggregation Function COUNT()

There are a number of important functions in MDX that return numeric values. Lets 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 definition—you can only use the Count() function, and other numeric functions, http://indectusa.com/wp-json/oembed/1.0/embed?url=http://indectusa.com/multi-function-sensors/ inside a calculated member!

WITH 
  MEMBER Measures.DistinctCountOfYears AS 
    Count([Date].[Calendar].[Calendar Year].MEMBERS) 
  MEMBER Measures.DistinctCountOfQuarters AS 
    Count([Date].[Calendar].[Calendar Quarter].MEMBERS) 
SELECT 
  {
    DistinctCountOfYears
   ,DistinctCountOfQuarters
  } ON COLUMNS
FROM [Adventure Works]

-- Removing unknown member
WITH 
  MEMBER Measures.DistinctCountOfYears AS 
    Count([Date].[Calendar].[Calendar Year].MEMBERS - [Date].[Calendar].[All].UNKNOWNMEMBER) 
  MEMBER Measures.DistinctCountOfQuarters AS 
    Count([Date].[Calendar].[Calendar Quarter].MEMBERS - [Date].[Calendar].[All].UNKNOWNMEMBER) 
SELECT 
  {
    DistinctCountOfYears
   ,DistinctCountOfQuarters
  } ON COLUMNS
FROM [Adventure Works]

--Adding filter
WITH 
  MEMBER Measures.DistinctCountOfYears AS 
    Count([Date].[Calendar].[Calendar Year].MEMBERS) 
  MEMBER Measures.DistinctCountOfQuarters AS 
    Count([Date].[Calendar].[Calendar Quarter].MEMBERS) 
SELECT 
  {
    DistinctCountOfYears
   ,DistinctCountOfQuarters
  } ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2007]
-- Generating incorrect results as where clause is not able to set context here

https://blog.oraylis.de/2008/12/existing-and-query-context/
The above query give the same result as previous queries, it seems to completely ignore our WHERE condition. The reason for that is that set operations like above inside of Calculated measures are not aware of the current context defined by our WHERE condition. This is the reason why in SQL Server 2005 the new EXISTING operator was added. The http://mikescarpetconnection.com/category/floor-coverings/laminate-flooring/ EXISTING operator forces the no prescription cytotec SET Operation(here MEMBERS function) to be computed inside the current context in Calculated Measures. So we’re simply adding this operator to our query like this:

--Adding filter with EXISTING clause
WITH 
  MEMBER Measures.DistinctCountOfYears AS 
    Count(EXISTING [Date].[Calendar].[Calendar Year].MEMBERS) 
  MEMBER Measures.DistinctCountOfQuarters AS 
    Count(EXISTING [Date].[Calendar].[Calendar Quarter].MEMBERS) 
SELECT 
  {
    DistinctCountOfYears
   ,DistinctCountOfQuarters
  } ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2007]

Problem Statement #1: Calculate count of quarters per year (Same Dimension)

Aggregating over EXISTING Pattern: We can use similar query as above but we now need YEAR members on Rows Axis.

-- GROUP BY DISTINCT type query
WITH 
  MEMBER Measures.DistinctCountOfQuarterPerYear AS 
    Count([Date].[Calendar].[Calendar Quarter].MEMBERS) 
SELECT 
  { DistinctCountOfQuarterPerYear } ON COLUMNS
  ,{ [Date].[Calendar].[Calendar Year].Members } ON ROWS
FROM [Adventure Works];

Since member defined is static in above query it gets results for all quarter instead of getting it for every year so to force calculation to be calculated for every year we can two methods
1) Use EXISTING inside COUNT function

-- GROUP BY DISTINCT type query
WITH 
  MEMBER Measures.DistinctCountOfQuarterPerYear AS 
    Count(EXISTING [Date].[Calendar].[Calendar Quarter].MEMBERS) 
SELECT 
  { DistinctCountOfQuarterPerYear } ON COLUMNS
  ,{ [Date].[Calendar].[Calendar Year].Members } ON ROWS
FROM [Adventure Works]

NOTE:: The EXISTING keyword is used within calculations to apply autoexists filtering to a set based on the other hierarchies from the same dimension. For sets with different dimensions existing is irrelevant since other set will always give total count. We will see how to achieve this in below section of this post.

WITH MEMBER MEASURES.ProductsCount AS 
    Count((EXISTING [Product].[Product].[Product].MEMBERS)) 
SELECT 
  { MEASURES.ProductsCount} ON COLUMNS
  ,{ [Date].[Calendar].[Calendar Year].Members } ON ROWS
FROM [Adventure Works];

http://sqlblog.com/blogs/mosha/archive/2008/10/22/optimizing-mdx-aggregation-functions.aspx
2) Using CURRENTMEMBER and DESCENDANTS functions together.

WITH 
	MEMBER [Measures].[DistinctCountOfQuarterPerYear 1]	        
                AS COUNT(EXISTING [Date].[Calendar].[Calendar Quarter].MEMBERS)
	MEMBER [Measures].[DistinctCountOfQuarterPerYear 2]	        
                AS COUNT(DESCENDANTS([Date].[Calendar].currentmember, [Date].[Calendar].[Calendar Quarter]))
        MEMBER [Measures].[DistinctCountOfQuarterPerYear 2 variation]
                AS DESCENDANTS([Date].[Calendar].currentmember, [Date].[Calendar].[Calendar Quarter]).count
SELECT {
		[Measures].[DistinctCountOfQuarterPerYear 1]
		,[Measures].[DistinctCountOfQuarterPerYear 2]
		} ON COLUMNS
  ,{ [Date].[Calendar].[Calendar Year].Members } ON ROWS
FROM [Adventure Works]

3) Using EXISTS and CURRENTMEMBER functions together.

WITH
  MEMBER Measures.DistinctCountOfQuarterPerYear AS 
    Count
    (
      Exists
      (
        [Date].[Calendar].[Calendar Quarter].MEMBERS
       ,[Date].[Calendar].CurrentMember
      )
    ) 
SELECT 
  {Measures.DistinctCountOfQuarterPerYear} ON COLUMNS
 ,{[Date].[Calendar].[Calendar Year].MEMBERS} ON ROWS
FROM [Adventure Works];

4) EXISTING vs CURRENTMEMBER
We want to count how many months are included in selected period(year). If 2 years are in selection, we expect to get the sum of both year’s values.
The “CountMonthsUsingCurrentMember” version takes all descendants of current member in Quarter level and counts them.
The “CountMonthsUsingExistingAndCurrentMember” version calls to an internal measure, similar as single version, for everyone of all selected members.

WITH
  MEMBER Measures.[CountMonthsUsingCurrentMember] AS
    COUNT(DESCENDANTS([Date].[Calendar].CURRENTMEMBER,[Date].[Calendar].[Calendar Quarter]))
  MEMBER Measures.[CountMonthsUsingExistingAndCurrentMember] AS
    SUM(EXISTING [Date].[Calendar].[Calendar Year].MEMBERS,Measures.[CountMonthsUsingCurrentMemberA])
  MEMBER Measures.[CountMonthsUsingCurrentMemberA] AS
    COUNT(DESCENDANTS([Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Calendar Quarter]))
SELECT {
    Measures.[CountMonthsUsingCurrentMember]
    ,Measures.[CountMonthsUsingExistingAndCurrentMember]} ON COLUMNS
FROM [Adventure Works]
WHERE ({[Date].[Calendar Year].&[2007] , [Date].[Calendar Year].&[2008]})

As we see calculation with just CURRENTMEMBER results in error and calculation with EXISTING generates correct results.

Problem Statement#2: Calculate count of products per month (Different Dimension)

Aggregating over EXISTS and NONEMPTY Pattern: To find the related members from two different dimensions through a fact table we can use the Exists() function. The Exists() function has three variants, as shown:
Exists( Set_Expression1, Set_Expression2)
Exists( Set_Expression1, Set_Expression2, MeasureGroupName )
Exists( Set_Expression1, , MeasureGroupName )

The first variant without the third argument Measure Group Name is useful for intersecting related attributes from the same dimension as shown in the previous recipe, Finding related members in the same dimension.
The second variant with the third argument Measure Group Name is ideal for combining dimensions across a fact table. In SSAS, a measure group represents a fact table.
The third variant omits the second argument for a set expression. What this variant does is instructs the engine to return distinct members from the first set that have valid combinations with the current member in context, that is the combinations have associated rows in the fact table of the specified measure group.
In our example, we have used the third variant, omitting the second set argument. The current member in context is every Month on rows([Date].[Calendar].CurrentMember). This query context is established in the evaluation phase of the query. There’s no need to use the current member as the second set; that member will be there implicitly.

WITH 
  MEMBER Measures.ProductsCount1 AS 
    Count
    (
      Exists
      (
        [Product].[Product].[Product].MEMBERS
       ,
       ,'Internet Sales'
      )
    ) 
  MEMBER Measures.ProductsCount2 AS 
    Count
    (
      NonEmpty
      (
        [Product].[Product].[Product].MEMBERS
       ,{[Measures].[Internet Sales Amount]}
      )
    ) 
SELECT 
  {Measures.ProductsCount1,Measures.ProductsCount2} ON 0
 ,[Date].[Calendar].[Month] ON 1
FROM [Adventure Works];

The queries in this recipe so far have illustrated the concept behind the EXISTS() and NONEMPTY() functions. These functions can be used to isolate related members on other dimensions. However, from the performance perspective, they are not great when you need to count members on other dimensions because the count-exists and the count-nonempty combinations are not optimized to run in block mode. The sum-iif combination on the other hand, is optimized to run in block mode. Therefore, whenever you need to do something more than simply isolating related members on other dimensions (such as counting, and so on), consider using a combination of functions that you know run in block mode.

WITH 
  MEMBER MEASURES.ProductsCount AS 
    Sum
    (
      [Product].[Product].[Product].MEMBERS
     ,IIF
      (
        IsEmpty([Measures].[Internet Sales Amount])
       ,null
       ,1
      )
    ) 
SELECT 
  {MEASURES.ProductsCount} ON 0
 ,[Date].[Calendar].[Month] ON 1
FROM [Adventure Works];