As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

CUBEVALUE and CUBEMEMBER

If you have not looked at the cube functions in Excel check out two of my previous posts: Excel BI Tip #18: Using Cube Functions to Break Out of Pivot Tables and Excel BI Tip #27: CUBESET and CUBESETCOUNT Functions. Cube functions allow us to apply MDX functionality in Excel beyond pivot tables and charts. In this post, I will show how you can expand the CUBEVALUE function by directly using member expressions or CUBEMEMBER functions in the syntax. Tip #18 demonstrates how to use cells in Excel.

Typically, you would use these functions separately. For example, CUBEMEMBER is used in column and row headers in a typical solution. Because of this CUBEVALUE usually refers to a series of cells and slicers.

CUBEMEMBER used as a column header:

=CUBEMEMBER(“AdvWorks”,”[Measures].[Customer Count]”)

CUBEVALUE referring to this member:

=CUBEVALUE(“AdvWorks”,$D8,F$7,Slicer_Commute_Distance)

The snapshot below shows how it looks in Excel when the cell with the CUBEVALUE function is selected:

Using Member Expressions and CUBEMEMBER

The method illustrated above is how the cells are made when a pivot table has been converted to formulas. However, if you look closely the CUBEVALUE function accepts CUBEMEMBER values. The next few examples show how you can use members directly in the name to get more functionality.

=CUBEVALUE(“AdvWorks”, “[Measures].[Customer Count]”,CUBEMEMBER(“AdvWorks”,”[Customer].[Gender].[Female]”))

=CUBEVALUE(“AdvWorks”, “[Measures].[Customer Count]”,”[Customer].[Gender].[Female]”)

You can use any MDX expression that returns a member as one of the expressions in the function. This includes functions like STRTOMEMBER which allows you to create dynamic functions to get current date. Using member expressions allow for even more customization without dependencies on reference columns.

Adding a Slicer

Finally, you can add a slicer reference to the CUBEVALUE function.

=CUBEVALUE(“AdvWorks”, “[Measures].[Customer Count]”,”[Customer].[Gender].[Female]”, Slicer_State_Province)