CODEWITHSUNDEEP
mdxmember
user1112251
user1112251

Reputation: 119

Getting first member with MDX

I want to get the expiration date of a product, however, there could be several expiration dates for the products

ProductID  | ExpDate | Qty
    1         FY12     1000
    2         FY12     1000
    2         FY14     1000
    3         FY13     1000
    3         FY15     1000

As you can see there are several dates for a Product, however, I just want the first ExpDate.

this is my query so far

SELECT NON EMTPY
([Product].[ProductID].[ProductID].MEMBERS *
[Primary Expiration Date].[Primary Expiration Year].[Primary Expiration Year].MEMBERS) on ROWS,
NON EMTPY ([Measures].[Qty]) on columns
FROM Sales_Market
WHERE(
{[Primary Expiration Date].[Primary Expiration Date Hierarchy].[Primary Expiration Quarter].&[FY12-Q4], 
[Primary Expiration Date].[Primary Expiration Date Hierarchy].[Primary Expiration Year].&[FY13], 
[Primary Expiration Date].[Primary Expiration Date Hierarchy].[Primary Expiration Year].&[FY14], 
[Primary Expiration Date].[Primary Expiration Date Hierarchy].[Primary Expiration Year].&[FY15]})

Any help?

Thanks

Upvotes: 0

Views: 1893

Answers (1)

Benoit
Benoit

Reputation: 1993

You can try this query:

SELECT NON EMTPY
Generate([Product].[ProductID].[ProductID].MEMBERS, Subset(NonEmpty({[Product].CurrentMember} * [Primary Expiration Date].[Primary Expiration Year].[Primary Expiration Year].MEMBERS), 0, 1)) on ROWS,
NON EMTPY ([Measures].[Qty]) on columns
FROM Sales_Market
WHERE(
{[Primary Expiration Date].[Primary Expiration Date Hierarchy].[Primary Expiration Quarter].&[FY12-Q4], 
[Primary Expiration Date].[Primary Expiration Date Hierarchy].[Primary Expiration Year].&[FY13], 
[Primary Expiration Date].[Primary Expiration Date Hierarchy].[Primary Expiration Year].&[FY14], 
[Primary Expiration Date].[Primary Expiration Date Hierarchy].[Primary Expiration Year].&[FY15]})

You can add a measure as second argument of NonEmpty if necessary.

Upvotes: 2

Related Questions