how to get Min values for dimension members?

Question

I have a cube with few dimensions. Report Date and Account are two of them. One account id may come under multiple Report dates. I need to find minimum report date for every accountid. Any ideas?

can you please add some code so we get an idea of the structure of your cube? — whytheq

SouravA SouravA · Accepted Answer · 2016-06-10T09:32:53

Maybe something similar -

WITH SET[MinDate] AS
    Head(NonEmpty(
            ReportDate.[ReportDate].[ReportDate].MEMBERS, 
            {(EXISTING [Account].[AccountId].CurrentMember , [Measures].[foo])}
            ),1)

SELECT [MinDate] ON 1,
[Account].[AccountId].MEMBERS ON 0
FROM [bar]
WHERE [Measures].[foo]

If what you want is the minimum date's value, then you would need to get that in a calculated measure instead of a set.

WITH MEMBER Measures.[MinimumDate] AS
    Head(NonEmpty(
            ReportDate.[ReportDate].[ReportDate].MEMBERS, 
            {(EXISTING [Account].[AccountId].CurrentMember , [Measures].[foo])}
            ),1).ITEM(0).Name

how to get Min values for dimension members?

1 Answers