Dynamic CUBESET list

[serky]

Hi

can someone help with CUBESET syntax? I have searched the Internet but had no luck.

I would like to create a dynamic list of stores based on the date selected for the report eg - December 14. I have a look-up table with store information including the date opened. If the date opened <= report date, I would like that store to be listed in the report.

I can get a list of all the stores as follows:
CUBESET("PowerPivot Data","[Stores].[Name].[All].Children","Store Names")

What do I need to add to this to only show stores open for a given report date?

Or is there a better way to get the list?

Thanks

 

[mneumann]

I believe you need to add a filter to the set expression, something along the lines of

CUBESET("PowerPivot Data","filter([Stores].[Name].[All].Children,[stores].[open date]<=Desired Date)","Store Names")

This works for me when I try to filter a cubeset by a measure, for some reason I have trouble getting it to work when trying to filter by another column. You could create an open date measure and then use

CUBESET("PowerPivot Data","filter([Stores].[Name].[All].Children,[measures].[open date]<=Desired Date)","Store Names")

 

[serky]

Hello

I tried a dummy variation to try and get it to work: CUBESET("PowerPivot Data","filter([Stores].[Name].[All].Children,[stores].[location]=3)","Store Names")
This returned a list but when I tried to use CUBERANKEDMEMBER to extract the values, I got a #N/A. Is this the problem you were talking about when using a column name?

I then tried to set up a measure for open date but it didn't work at all (have to admit I am really struggling with DAX).

Do you have an example of the measure syntax if I were to try either [location] or [open date]?

Thanks