Search code examples
sqlsql-server-2008ssasmdxcube

How to get max value using MDX Query

I'm trying to get maximum value for Dimension table where the data as referred with Fact table

WITH 
  MEMBER [Measures].[Max key] AS 
    Max
    (
      [DimAnchorDate].[Date Key].MEMBERS
     ,[DimAnchorDate].[Date Key].CurrentMember.Member_Key
    ) 
SELECT 
  {
    [Measures].[Max key]
  } ON COLUMNS
FROM X;

This query is giving me output : 20141231 In FactTable we have data upto 20141031. From the above query I would like to get 20141031

enter image description here

So now I am trying to get max value from DimAnchordate table as same as it is coming in FactPatientDr Table (i.e 20141031).So please suggest me the best way to achieve this...

Solution

  • I think at the moment you are looking at Dates that are empty in certain parts of the cube space - try forcing to nonempty by using a measure from the FactPatientDr 

    WITH 
  MEMBER [Measures].[Max key] AS 
    Max
    (
      nonempty(
        [DimAnchorDate].[Date Key].MEMBERS
        ,[Measures].[SomeMeasureInFactPatientDr]
      )
     ,[DimAnchorDate].[Date Key].CurrentMember.Member_Key
    ) 
SELECT 
  {
    [Measures].[Max key]
  } ON COLUMNS
FROM X;