Search code examples
sqlmdxcubeolap-cube

How to get number of the same members in mdx?

I would like to determine the number of particular weekdays (e.g. Mondays, Tuesdays, and so on....) between two dates. I thought something like the following should work, but member returns 1.

What have i done wrong? 

WITH 
  MEMBER measures.NumberOfSameWeekDays AS 
    Count([Dim Date].[Day Of Week].CurrentMember) 
SELECT 
  measures.NumberOfSameWeekDays ON COLUMNS
 ,[Dim Date].[Day Of Week].[Day Of Week] ON ROWS
FROM [test]
WHERE 
  (
    [Dim Client].[Common Client UID].&[{ED8822E7-2873-4388-BC3A-CC553D939FC4}]
   ,
    [Dim Date].[Date Int].&[20150701] : [Dim Date].[Date Int].&[20150731]
  );
Solution

  • This is a proof of what is happening:

    WITH 
  MEMBER measures.NumberOfSameWeekDays AS 
    Count([Date].[Day of Week].CurrentMember) 
  MEMBER measures.WeekDayCurrentMem AS 
    [Date].[Day of Week].CurrentMember.Member_Caption 
SELECT 
  {
    measures.NumberOfSameWeekDays
   ,measures.WeekDayCurrentMem
  } ON COLUMNS
 ,[Date].[Day of Week].[Day of Week] ON ROWS
FROM [Adventure Works]
WHERE 
    [Date].[Calendar].[Date].&[20050101]
  : 
    [Date].[Calendar].[Date].&[20050116];

    Here is the result of the above:

    enter image description here

    Here is a solution to the above behaviour:

    WITH 
  MEMBER measures.NumberOfSameWeekDays AS 
    Count
    (
      (EXISTING 
        [Date].[Day of Week].CurrentMember * [Date].[Calendar].[Date])
    ) 
SELECT 
  {
    measures.NumberOfSameWeekDays
  } ON COLUMNS
 ,[Date].[Day of Week].[Day of Week] ON ROWS
FROM [Adventure Works]
WHERE 
    [Date].[Calendar].[Date].&[20050101]
  : 
    [Date].[Calendar].[Date].&[20050131];

    This returns the following:

    enter image description here

    A simplified version of Sourav's answer - although still rather complex - and potentially slow as it uses Generate which is iterative:

    WITH 
  MEMBER Measures.CountOfDays AS 
    Generate
    (
      (EXISTING 
        [Date].[Date].[Date].MEMBERS)
     ,[Date].[Day of Week]
     ,ALL
    ).Count 
SELECT 
  Measures.CountOfDays ON 0
 ,[Date].[Day of Week].[Day of Week].MEMBERS ON 1
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].&[2005] : [Date].[Calendar].&[2006];