Member with Named set returns always the same value in SSAS

Question

I'm needing to query a cube as a plain table. Also, i need to use a named set for performance reasons (the query takes 10x if not using the set). The issue is that i'm getting the same value for every row for the Date Time calculated member. BTW, i'm using this member cause i haven't found any way to query a named set 'on columns'.

The query looks like this:

with
set [CurrentDates] as filter([Time].[Date].Members, not isempty([Measures].[Net Sold Units]))
member [Measures].[Date Time] as [CurrentDates].Item(1).Member_Key
select
{
[Measures].[Date Time],
[Measures].[Song Name]
--other calculated members
}
on columns
,
subset
(
    {
        order
        (
            except
            (
                NONEMPTY([Trend Transaction].[Trend Transaction].Members, [Measures].[Net Sold Units]), 
                [Trend Transaction].[Trend Transaction].[All]
            ),
            [Measures].[Date Time], basc
        )
    }
    ,0, 50)
on rows 
from Trends
where
(
{[Time].[Date].&[2012-09-01T00:00:00],[Time].[Date].&[2012-09-02T00:00:00]}
)

And the result i'm getting looks like this:

Date Time Song Name

---

9/1/2012 Where Have You Been 9/1/2012 We Are Young 9/1/2012 Wide Awake (Origionally Performed by Katy Perry) [Karaoke Version] 9/1/2012 Breathing 9/1/2012 So Sophisticated (Originally Performed by Rick Ross) [Karaoke Version] . . .

The dates for the last songs is obviously wrong, should e 9/2/2012

I recognize that i'm kinda SSAS newbie, probably there is something i'm missing here :)

Is there any way to do that i'm needing?

Thanks in advance!

Answer 1

Well, the solution was finally to have the set and the member like this:

set CurrentDates as filter([Time].[Date].[Date], not isempty([Measures].[Net Sold Units]))
member [Measures].[Date Time] as CDATE(NONEMPTY(CurrentDates, [Measures].[Net Sold Units]).Item(0).Member_Key)

The key is to have the named set into the SCOPE, to have it referenced in the member.