# Consult a cubemember with another cubemember filter



## wlandim (Sep 27, 2016)

Hi,

I have a table that has, among other information, three that I use most:

OC nº              Suplier                Value
1111               abc                      20,00
1112               def                      40,00
1113               ghi                       60,00

With powerpivot is easy to identify the value of the OC by cubevalue, thats ok.

I'd like to make a filter to show the name of the suplier filtered by OC nº. 

I tried to use cubemember and cubeset but I was in wrong way

Can anyone help about this?


----------



## theBardd (Oct 4, 2016)

I am not sure I fully understand. Can you give an example of what you want to see?


----------



## macfuller (Oct 5, 2016)

I think I understand - if one value is chosen in a slicer but there are other properties to that value, you'd like to display that property.

This is poorly documented and I stumbled upon this solution by accident, but it works for me.  If there's a shorter way I hope someone else can provide it.
Assume the table is "Supplier Info" and the slicer name is Slicer_OC_Value... the property name from the OC table you want to retrieve is "Supplier Name" e.g. "abc"

If the slicer is showing a value (e.g. OC#) use a CUBE function to display that value in a cell (say $D$2)
=CUBERANKEDMEMBER("ThisWorkbookDataModel",Slicer_OC_Value,1)

Now reference $D$2  in a new cell with this formula

=CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel","([Supplier Info].[OC].["&$D$2&"],[Supplier Info].[Supplier Name].children)"),1)

The order of the selection in the CUBESET is important - lookup value first, then the .children of what you want to return.

This also assumes that each lookup value in your Supplier_Info table is unique, so selecting OC returns only one corresponding Supplier Name property.

As I said, I hope someone has a more elegant solution!


----------



## wlandim (Nov 1, 2016)

Great!!!!!!! It works very well!! Thanks a lot Macfuller, everything I need I can get throught your formula!!!!!


----------



## ImkeF (Nov 1, 2016)

How nice! Another elegant alternative for the missing CUBEMEMBERPROPERTY-function in Power Pivot and Tabular. 
(So far my favourite has been Matt Allingtons recommendation to create a text-measure instead).

Attention for users with ";" as separators: You have to keep "," as a separator for the cubeset-expression within the brackets, otherwise it wouldn't work:
([Supplier Info].[OC].["&$D$2&"]*,*[Supplier Info].[Supplier Name].children)

Replace the other ones with ";".


----------



## theBardd (Nov 2, 2016)

ImkeF said:


> How nice! Another elegant alternative for the missing CUBEMEMBERPROPERTY-function in Power Pivot and Tabular.



I came up with an emulation using CUBEMEMBER and MDX EXISTS in  a blog post so time back https://wessexbi.wordpress.com/2014/02/16/a-cubememberproperty-equivalent-with-powerpivot/ (You even commented in that post Imke )


----------

