Since my last post on the Excel cube functions proved to be very popular, I thought I’d write a follow-up that shows off some more little-known but interesting functionality: how to use the CubeSet() function to combine selections from multiple slicers.
The Problem
Consider the following Power Pivot model based on data from the Adventure Works DW database:
The Internet Sales table contains one row per sales transaction; Product and Date are dimension tables that provide more information on the product bought in the transaction and the date the order was taken on. There’s one measure called Distinct Customers that returns the distinct count of customers; its definition is as follows:
[sourcecode language='text' padlinenumbers='true']
Distinct Customers:=DISTINCTCOUNT('Internet Sales'[CustomerKey])
[/sourcecode]
With this model you could build the following simple report by converting a PivotTable to cube functions:
Obviously this will tell you how many distinct customers bought the products selected in the slicer each year. You could also duplicate the slicer and the cube functions and have two reports side-by-side so you could compare the number of customers that bought two different groups of products:
The question here is, though, how can you create a third report that shows the number of distinct customers that bought any of the products selected across both slicers? So, using the example above, the report on the left shows the number of customers that bought Mountain-200 Black 38, Mountain-200 Black 42 or Mountain-200 Black 46; the report on the right shows the number of customers that bought Mountain-200 Silver 38, Mountain-200 Silver 42 or Mountain-200 Silver 46; the aim is to create a third report that shows the number of distinct customers that bought Mountain-200 Black 38, Mountain-200 Black 42, Mountain-200 Black 46, Mountain-200 Silver 38, Mountain-200 Silver 42 or Mountain-200 Silver 46 without having to select all those products in a third slicer.
The Solution
Step 1
The first thing to point out is that you can use the CubeSet() function to return the set of items selected in an Excel slicer. This is a widely-known technique – Rob Collie has a great post describing it here. What I didn’t realise until recently though was that one CubeSet() function can point to other cells containing CubeSet() functions and union all the items returned by those CubeSet() functions. This allows you to combine the selections made in multiple slicers, assuming that those slicers refer to the same field in your Power Pivot model. Here are the formulas in three cells that do this:
In cell I2 the formula uses the CubeSet() function to capture the selection made in the left-hand slicer in the report above, captioned Product Selection A, which has the name Slicer_Product. In cell I3 there is a similar formula to capture the selection made in the slicer on the right in the report, captioned Product Selection B, which has the name Slicer_Product1. The formula in I4 then uses a third CubeSet() function to combine the sets returned by the last two formulas into a third set.
Step 2
Now that you have a single CubeSet() formula in I4 that combines the selections in both slicers, you can use the trick I showed in my last post and reference that cell in CubeValue() formulas to get the number of distinct customers that bought any of the products listed in either slicer:
Here’s what the report looks like at this point:
Step 3
Notice that, thanks to the power of Power Pivot, customers are not double-counted in the new combined selection report on the right-hand side. For example, in 2003 841 customers bought one or more products selected in the slicer Product Selection A while in 2003 796 customers bought one or more products listed in the slicer Product Selection B. 841+796=1637, but in the combined selection report correctly states that only 1588 distinct customers bought products listed in either Selection A or Selection B. We can use these numbers to do one final, useful calculation: 1637-1588=49, so there were 49 customers who must have bought products in both Selection A and Selection B. The formula to do this is pretty straightforward and looks like this:
[sourcecode language='text' ] =IFERROR(($C19+$G19)-$J19, 0) [/sourcecode]
You can download the sample workbook for this post here.
I never thought about this combination! It is great with power pivot. Thnx for sharing mate
Hey Chris, could you help me and the community take this one step further? Most of the examples you list eventually have slicers filtering results in a =cubevalue() function.
How about a slicer (or named range of slicers/cubeset functions) to influence a [Table].[column].children list inside a =cubeset function. Based on previous posts I’m familiar with pulling values from a cubeset list with =cuberankmember() and ultimately we can use what you list above (or named ranges) inside the =cubevalue() functions
I’m assuming it has to involve a tuple “{(input1,input2)}” in the set_expression parameter (if the tuple is in the sort_by parameter some funky things happen at the end of the list).
Here is the example.
Slicer >> List of industries (apprx 15 total). Some perform very similar to one another
List >> List of clients that are rolled up underneath each industry (at least 5-15 per industry)
Sort >> Sales metric
Code that works (Semi-hard coded)
A1 is a data validation drop down field of just the industry values. Its limited due to only one industry input
=CUBESET(“ThisWorkbookDataModel”,
“{([summary].[industry].[“&A1&”],[summary].[client].children)}”,
“Top Clients”,
2,
[Measures].[Sales]
)
Code that does not work
=CUBESET(“ThisWorkbookDataModel”,
“{(“&CUBESET(“ThisWorkbookDataModel”,Slicer_industry,”Slicer_industry”)&”,[summary].[client].children)}”,
“Top Clients”,
2,
“[Measures].[Sales]”
)
Code that works but does not solve for when n number of values in the industry slicer are selected
=CUBESET(“ThisWorkbookDataModel”,
“{(“&CUBERANKEDMEMBER(“ThisWorkbookDataModel”,Slicer_industry,ROW(A1))&”,[summary].[client].children)}”,
“Top Clients”,
2,
“[Measures].[Sales]”
)
Any one have thoughts on syntax that will work? (edited post comment v2)
I was hoping this could be done in other ways other than via MDX (like you mention in this post: https://blog.crossjoin.co.uk/2012/10/22/returning-selected-items-in-an-excel-slicer-using-mdx-in-powerpivot-and-ssas/). I may have issues downloading the OLAP PowerPivot Extension given corporate firewall restrictions on apps/extensions.
I’ve also tried the following based on the last few sentences in this blog post: https://blog.crossjoin.co.uk/2014/06/20/using-slicer-selections-in-the-cubeset-function/
In another tab create a list
All [summary].[industry].[<>] are referenced as a named range “Slicer_industry_namerange”
Function for value in Slicer_industry_namerange
=IF(
ISNA(CUBERANKEDMEMBER(“ThisWorkbookDataModel”,Slicer_industry,ROW(A1))),
“”,
“[summary].[industry].&[“&CUBERANKEDMEMBER(“ThisWorkbookDataModel”,Slicer_industry,ROW(A1))&”]”
)
for row(a1) returns: [summary].[industry].[AUTOMOTIVE]
for row(a2) returns: [summary].[industry].[HOME_GOODS]
…based on what was in the Slicer_industry.
I tried referencing the named range in the CubeSet but returns the error: “A value is not available to the formula or function”. I’m assuming something is slightly off in the underlying syntax of the named range (does it need to be set to TEXT() vs a tuple of a tuple?)
=CUBESET(“ThisWorkbookDataModel”,
“{(“&CUBESET(“ThisWorkbookDataModel”,Slicer_industry_namerange)&”,[dlrsummary].[client].children)}”,
“Top Clients”,
2,
“[Measures].[Sum of app_M36]”
)
The last metric should have said “[Measures].[Sales]”
The bad news is that you’ve hit one of the fundamental (and frustrating) limitations of the Cubeset function. There isn’t any way to manipulate the contents of the set that Cubeset returns with further MDX, and the only thing to do is to extract the individual members in the set using CubeRankedMember and then construct a completely new set, with any extra transformations you want, from that.
Are you able to point me in the direction of some blog posts that talk about creating sets (using MDX through Calculations >> Fields, Items, Sets after clicking on a pivot table that references my cube) based on cells in Excel?
Based on my comments above, I’ve been able to extract the current individual members in the slicer and have aggregated the output into a single cell (string formatting and concatenation).
For example two items are selected from Slicer with first item formatted output in cell B2 as [summary].[industry].[AUTOMOTIVE], and cell b3 [summary].[industry].[HOME_GOODS]. Finally in B4 I have both/N together ([summary].[industry].[AUTOMOTIVE], [summary].[industry].[HOME_GOODS]). I was thinking that cell B4 could be referenced inside the Set, but that might not be right.
Based on other blog posts it appears that MDX functions FILTER(), TOPCOUNT() and EXISTS() may need to be used inside the set but all examples appear to be static and not dynamic based on the output from B4.
I was able to figure out the solution you mentioned. It mainly involves using this Cubeset function, then pulling values from this dynamically created cubeset with CubeRankMember.
CUBESET(“ThisWorkbookDataModel”,
“FILTER([summary].[clients].children, [summary].[industry] = “&cell reference&”)”,
“This worked Dynamic filter set based on slicer”,
2,
“[Measures].[Sales]”
)
The output of the cell reference looks like “AUTOMOTIVE OR HOME_GOODS” (without the quotes)
which is dynamically built based based on a bunch of CUBERANKMEMBER cells that pull the values of the active slicer (Slicer_industry).
First, I didn’t know I could use DAX/MDX functions (i.e. FILTER() ) inside of Excels regular cell formulas (likely only works in CUBE functions?). Second, I had trouble figuring out the syntax. At first I tried creating a list “(AUTOMOTIVE, HOME_GOODS)” or “{AUTOMOTIVE, HOME_GOODS}” or “([summary].[client].[AUTOMOTIVE] OR [summary].[client].[HOME_GOODS])” but realized that the output of the cell reference should be “AUTOMOTIVE OR HOME_GOODS” similar to what you would put in a CALCULATE(measure,FILTER(..,..)) in PowerPivot.
Perhaps there is a more optimal way to concatenate the cell reference string, but for now it appear to be working.
Thanks again for your help and putting this blog together.
Hi Chris,
I am relatively new to the CUBE functions so forgive me if this is a rather obvious question but how did you create the 2 slicers in the above example that reference the same data field but allow independent selections to feed the CUBESET formula? I have been trying to replicate this and when I add a 2nd slicer referencing the same data field the “Name to use in formulas:” remains the same for both slicers whereas in your example you have Slicer_Product and Slicer_Product1.
im just starting to use Powerpivot and now CUBEVALUE and have found your blog/posts invaluable in learning these great tools
Thanks
Hmm, not an obvious question at all! I’m pretty sure it used to be easier than this, but right now you need to create two separate PivotTables and then click on each in turn and add a slicer to get different name-to-use-in-formulas. If you try to add a slicer from the same field twice in the same PivotTable you get two slicers with the same name-to-use-in-formulas.