Consult a cubemember with another cubemember filter

[wlandim]

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]

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

 

[macfuller]

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]

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

 

[ImkeF]

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]

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 )