Getting Sort by in CUBESET formula to respect other filters

[mneumann]

I am currently creating a dashboard type list of products sold with multiple measures, example below. There are a total of 104 products.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Product Name[/TD]
[TD]Total Sold[/TD]
[TD]Total Sale $[/TD]
[TD]Total Returned[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bike[/TD]
[TD]50[/TD]
[TD]$5000[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Car[/TD]
[TD]30[/TD]
[TD]$12000[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

Due to needing to filter this by Customer as well, I am using cube formulas instead of a normal PivotTable. We have 20k+ customers and 100k+ contacts within the customer list and need to be able to enter a customer number or contact name into a drop down and get the table to update, and drop downs and cube formulas are easier for my users than a 20k button slicer.

So when my user enters a customer number, the table for customer 1 looks like this

[TABLE="class: grid, width: 500"]

<tbody>[TR]
[TD]Product ID[/TD]
[TD]Product Name[/TD]
[TD]Total Sold[/TD]
[TD]Total Sale $[/TD]
[TD]Total Returned[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bike[/TD]
[TD]6[/TD]
[TD]$800[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Car[/TD]
[TD]8[/TD]
[TD]$1400[/TD]
[TD]0[/TD]
[/TR]
</tbody>
[/TABLE]

All this is working great.

My problem is coming when I try to create sort options for the table. I have a slicer from a disconnected table that gives all the sort options (Total Sold, Total Sale $,Total Returned) and a formula in A3 as follows:

=IF(CUBERANKEDMEMBER("PowerPivot Data",Slicer_Sort_By,1)="All","Total Sold",CUBERANKEDMEMBER("PowerPivot Data",Slicer_Sort_By,1))

So A3 returns the sort slicer selection or returns Total Sold as default if nothing is selected.

In A1 I have a cubset formula as follows:

=CUBESET("PowerPivot Data","[Products].[Product ID].children","Products Cubeset",2,"[Measures].["&$A$3&"]")

and down Row B is my Product IDs with the following formula spanning 104 rows

=CUBERANKEDMEMBER("PowerPivot Data",$A$1,ROW(A1))

When I use my Sort by Slicer to select a sort, it sorts, but it sorts by totals, not by totals for the selected customer, so once sorted by total sold, the second table above will still have bike first as it is the highest selling total item, even though customer 1 bought more cars.

I have the customer number returned in A4, so I think if its possible I just need to add something to the last argument of the cubset function to make it look like

=CUBESET("PowerPivot Data","[Products].[Product ID].children","Products Cubeset",2,("[Customers].[CustomerID].["&$A$4&"],[Measures].["&$A$3&"]"))

but I've tried this and many other variations and can't get anything but N/A errors.

Anybody know if its possible to sort by multiple paramaters/levels like this and if so if I am on the right track or how I would do it?

There will ultimately be 2 cells and 4 slicers that I will want the sort to comply with, but once I have the syntax right I'm sure I can figure out the rest.

Thanks and Happy New Year

 

[scottsen]

Issues with building cube formulas dynamically come up on the forum every once in awhile ... and people usually work through it by trial and error... so you might by try loading more of your formula into a separate cell ... or something.

I have used the technique you describe (sort order defined by a disconnected slicer), but never combined it w/ cube formulas. I wasn't totally clear on why cube formulas were required though? At a light glance it certainly looks like a normal pivot table would work...

 

[mneumann]

Hey Scott,

Thanks for the reply. A normal pivot would technically work, but the issue is trying to avoid a report filter or slicer with 20k+ customer numbers and another with 100k+ contact names within the customers. I have a sheet set up in the workbook so the end user can enter a customer number into a cell and then the cube formulas alter the table to show only items from that customer. There's also VBA that runs behind the scenes that activates another cell when the customer number is entered to pull the sub-contacts from that customer that can be selected through what becomes a short data validation list that drills the cube fomula table down even further.

My first attempt was to try to write VBA to get the customer number and contact selection to activate hidden slicers so I could just use a normal pivot table, but that proved to be over my head, so I went ahead and just built the whole workbook out with Cube Formulas as I knew it would work. I've got everything I need except for this sort now. I will keep trying the guess and check and hopefully come across the correct syntax if it exists.

 

[mneumann]

Problem solved thanks to Chris Webb's intro to MDX series in his blog I was able to figure out syntax and figure out that the sort by condition needs to be in curly and round brackets to be defined as a set of tuples, the correct syntax appears to be

"{([Customers].[CustomerID].["&$A$4&"],[Measures].["&$A$3&"])}"

 

[youngkrs]

I know this is two year later but pertains to this thread.

Did you ever try passing more than one customerID into your report. For example a user wanted to see the results for customer #1 and #3? I've been playing around with the set_expression parameter in the CubeSet function but even if the values are in a tuple with the {( )} brackets, I only get a valid cubeset formula result when there is one value per data model field. Another way to think about it is to have a slicer (with more than one items selected) as an option inside a cubeset function.

Problem solved thanks to Chris Webb's intro to MDX series in his blog I was able to figure out syntax and figure out that the sort by condition needs to be in curly and round brackets to be defined as a set of tuples, the correct syntax appears to be

"{([Customers].[CustomerID].["&$A$4&"],[Measures].["&$A$3&"])}"