CODEWITHSUNDEEP
sqlsql-server-2008ssasmdxcube
mohan111
mohan111

Reputation: 8865

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...

Upvotes: 3

Views: 9434

Answers (2)

Amrit Tiwari
Amrit Tiwari

Reputation: 11

I think we can use Tail function to get desired result with measure value as well:

select non empty([Measures].[Entered Case],
    tail  
   (Order   
      ([Calendar].[Date].[Date].members  
         ,[Measures].[Entered Case]
         ,basc
      )  
   ,1
   ))   on 0 from [SolutionPartner]

Upvotes: 1

whytheq
whytheq

Reputation: 35557

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;

Upvotes: 3

Related Questions