with 


 MEMBER [Measures].[Label] AS [RDate].[Date].CURRENTMEMBER.Item(0).Name


SELECT non empty({[Measures].[Label]},{[Measures].[Rentals]}) on columns,
non empty( Generate
  ( [Title].[Format].[Format].MEMBERS
    ,TopCount
    (
      Order
      (Filter([Title].[Format].CurrentMember, [Title].[Format].Member_Value = "DVD" or [Title].[Format].Member_Value = "Blu-ray" )
        * [Title].[Format-Titles].[Long Title Name].MEMBERS
       ,[Measures].[Rentals]
       ,DESC
      )
     , 50
    )
  ) ) ON rows

      FROM [MasterCube]
      Where  [Date].[Date].[2015-08-09]:[Date].[Date].[2015-08-17]

i am trying to get top  50 movies by format,   

With out [Measures].[Label]  column query works, but trying to add release date for that title,

dimension is [RDate].[Date],

I am expecting result

DVD  Tilte1      01/01/2000    1,232

DVD  Tilte2      08/03/2006      922

Thanks

V

  

• Edited by Vaishu 9 hours 53 minutes ago change

Hi Vaishu,

According to your description, you want to show the release date as a measure in your MDX query. Right?

In this scenario, I think the release date should be unique for each movie, it's better to make it to be an attribute in Movie dimension associated with each movie. Now since you already create a Release Date dimension, we can't display the Release Date directly based on the Movie. However, we can have the existing set crossjoin the RDate members so that the Non Empty corresponding RDate will also appear on rows. Even we can create a measure the show the RDate member name, but we still have to make the RDate members appear on rows. Please refer to sample below:

with set [a]
as
filter([Date].[Calendar].[Calendar Year].members*
[Product].[Product Categories].[Category].members,[Measures].[Internet Sales Amount]>0)
member [measures].[date]
as
[Date].[Calendar].currentmember.name
select {[measures].[date],[Measures].[Internet Sales Amount]} on 0,
[a] on 1
from
[Adventure Works]

Regards,