CODEWITHSUNDEEP
ssasmdxolap
whytheq
whytheq

Reputation: 35557

Limiting date range using Filter

Without using the colon operator how do I filter the set AllDates to just 05Jan2005 to 10Jan2006?

WITH 
  SET [AllDates] AS 
    [Date].[Date].[Date].MEMBERS 
  MEMBER [Measures].[DTkey] AS 
    [Date].[Date].CurrentMember.Member_Key 
  MEMBER [Measures].[DTmemValue] AS 
    [Date].[Date].CurrentMember.MemberValue 
  MEMBER [Measures].[DTvalue] AS 
    [Date].[Date].CurrentMember.Value 
SELECT 
  {[Measures].[DTmemValue]} ON 0
 ,Filter
  (
    [AllDates]
   ,
    [Measures].[DTmemValue] > 0
  ) ON 1
FROM [Adventure Works];

Upvotes: 0

Views: 169

Answers (2)

SouravA
SouravA

Reputation: 5243

I don't have Adv Wks but tested the below on mine and it worked well.

Is this what you're looking for?

WITH 
  SET [AllDates] AS 
    [Date].[date].[date].members
  MEMBER [Measures].[DTkey] AS 
    [Date].[date].CurrentMember.Member_Key 
  MEMBER [Measures].[DTmemValue] AS 
   [Date].[date].CurrentMember.MemberValue 
  MEMBER [Measures].[DTvalue] AS 
    [Date].[date].CurrentMember.Value 
SELECT 
  {[Measures].[DTmemValue]} ON 0
 ,Filter
  (
    [AllDates]
   ,
   CDate([Measures].[DTmemValue]) >= CDate("01/05/2005") 
   and CDate([Measures].[DTmemValue]) <= CDate("01/10/2006")
  ) ON 1
FROM [Adventure Works];

Upvotes: 1

BI Dude
BI Dude

Reputation: 2016

WITH 
  SET [AllDates] AS 
    [Date].[Date].[Date].MEMBERS 
  MEMBER [Measures].[DTkey] AS 
    [Date].[Date].CurrentMember.Member_Key 
  MEMBER [Measures].[DTmemValue] AS 
    [Date].[Date].CurrentMember.MemberValue 
  MEMBER [Measures].[DTvalue] AS 
    [Date].[Date].CurrentMember.Value 
SELECT 
  {[Measures].[DTmemValue]} ON 0
 ,Filter
  (
    [AllDates]
   ,
    CDate([Measures].[DTmemValue]) > CDate("2006-01-01")
  ) ON 1
FROM [Adventure Works];

More details about MDX Filtering can be found: http://chrish.com.au/blog/filtering-in-mdx/

Upvotes: 1

Related Questions