Newbie cubeset function

[somnifac]

Ok, I'm an extreme newbie when it comes to doing a whole lot with PowerPivot. I've managed to pull data from the data source using various permutations of CUBEVALUE functions, and creative sorting and filtering. I've been able to generate Top 10 reports and the like, and replace all sorts of reports that were all manual processes at one point.

What I'm trying to do now, it generate a cube with a CUBESET function that contains the IDs of all records that meet a specific criteria. An example of the structure of my data is below (the actual data set is much larger):

​

Let's say in this example I want to generate a cube of all IDs for Product 2, the 10th month, and the year 2012. In that case there are 2 rows that meet this criteria, IDs 20 and 34. If only it were that easy when going through the real data.

I've put something together that sort of works, but it takes FOREVER for the query to run. We're talking minutes upon minutes upon minutes. Not useful at all. I cobbled it together based off of the usable Top X formulas I'd put together.

Now, I'm sure there must be a right way to do it, but nothing I've put together actually works.

Would anyone be able to maybe point me in the right direction?

Thanks in advance for any help anyone can provide.

 

[scottsen]

I would really need to look in more detail to say why things are slow, but fyi... the 2nd param to cubeset is full mdx, so you could do something like:

=CUBESET("PowerPivot Data", "TOPCOUNT([Table1].[Blue].[All].children, 3, [Measures].[xxx])", "my set")
or
=CUBESET("PowerPivot Data", "FILTER([Table1].[Blue].children, [Table1].[Blue].CURRENTMEMBER.Name= ""3"")", "my set")

I'm not great with MDX, so I am hesitant to attempt an ACTUAL answer to your question