Cube formulas

[Matias D]

Hello,

I am new to the cube functions and also new to this forum so any help is greatly appreciated.
I am working with a simple table that has 2 columns, Column A: Person with text values Anna, Chuck, Kelly and Cassandra. Column B: Region with text values central, west, south and east.

Using the cubeset and cuberankedmemeber i am able to produce a list that contains the person names. But then i am not able to produce a cube formula that retrieves the region name for each person name. It is possible to do this using cube formulas? I tried using cubset and cuberankedmember but not any positive result. Thanks!

 

[kennypete]

Welome @Matias D - An initial warning, I am a newbie with cube formulas in Pivot Tables, so take this for what it is. Using a table similar to yours and (a) creating a connection and Pivot table, (b) a copy of that converted to formulas (with the RegionL being a function shown in the screenshot), you can access the regions with a formula, but perhaps not in a straight-through way (because I don't know whether the function can be replicated in an in-line, standalone cell's formula). Anyway, perhaps this will help:

Book1
	A	B	C	D	E	F	G	H
1	Person	Region		Row Labels	RegionL		Row Labels	RegionL
2	Ann	W		Ann	W		Ann	W
3	Chuck	E		Cass	S		Cass	S
4	Kel	C		Chuck	E		Chuck	E
5	Cass	S		Kel	C		Kel	C
6				Grand Total	W.E.C.S		Grand Total	W.E.C.S
7
8	Table1			Converted to formulas			Pivot Table
Sheet1

Cell Formulas
Range	Formula
E1	E1	=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[RegionL]")
D2	D2	=CUBEMEMBER("ThisWorkbookDataModel","[Table1].[Person].&[Ann]")
E2:E6	E2	=CUBEVALUE("ThisWorkbookDataModel",$D2,E$1)
D3	D3	=CUBEMEMBER("ThisWorkbookDataModel","[Table1].[Person].&[Cass]")
D4	D4	=CUBEMEMBER("ThisWorkbookDataModel","[Table1].[Person].&[Chuck]")
D5	D5	=CUBEMEMBER("ThisWorkbookDataModel","[Table1].[Person].&[Kel]")
D6	D6	=CUBEMEMBER("ThisWorkbookDataModel","[Table1].[Person].[All]","Grand Total")

 

[Matias D]

Ohhhh yes many thanks!, looks like the key is to define a measure using the concatenatex function to then retrieve using cubevalue function.
Thanks again!

 

[Matias D]

Thanks @kennypete. I found this alternative formula for the measure definition:
IF(HASONEVALUE(Table1[Person]),VALUES(Table1[Region]))

 

[macfuller]

It's disappointing that something so useful and popular is so oddly difficult to achieve. There's no documentation on this except in occasional spots on the web. The cube formula to obtain the value of a row in the same table but a different column than the one you have selected is:

a) a slicer returns the value you're using for a lookup to (say) cell $B$3. This formula in cell $B$3 returns the selected slicer value
=CUBERANKEDMEMBER("ThisWorkbookDataModel",Slicer_SlicerName,1)
you can have a different way to get the lookup value you want, you just need that value to pass to the 2nd formula below.

b) this formula takes the slicer selected value and returns the other column value associated with the same row
=CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel","([SourceTable].[SelectedColumn].["&$B$3&"],[SourceTable].[ReturnColumn].children)"),1)

MSFT should really put out better documentation on the CUBE formulas!

 

[Matias D]

Thank you @macfuller . Totally agree with you that the cube formulas documentation is very poor. In other words the idea is to set a cubeset that has 1 child from the return column that results from the combination of selected column & cell B3. Then use cuberankedmember to retrieve this only child as a result. Using this method we can avoid creating a measure as described above. Thanks again. Always happy to learn something new