CODEWITHSUNDEEP
sqlmdxcubeolap-cube
Timsen
Timsen

Reputation: 4126

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]
  );

Upvotes: 1

Views: 92

Answers (2)

whytheq
whytheq

Reputation: 35557

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];

Upvotes: 1

SouravA
SouravA

Reputation: 5243

Adventure Works version:

WITH MEMBER Measures.CountOfDays  AS
GENERATE
    (
     EXISTING [Date].[Date].[Date].MEMBERS,
     EXISTING [Date].[Day of Week].[Day of Week].MEMBERS
     ,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]

The GENERATE part gets all the days of weeks in current context and based on whatever filter you might have.

Upvotes: 1

Related Questions