3

I am trying to get the values with wildcard in Cubevalue formula(below) in excel. I am not finding any solution. 

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Bookings_Net]","[Dashboard_Data].[Level_1].[Karnataka_India]")

I am trying to get the values where [level 1] ends with [_India], I don't want to create a calculated Column in Data Model as this condition may be used for different columns and different conditions.
I have also tried by giving cell reference(eg-[Cell A1] = "_India) as like below but I am not able to get the results.

CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Bookings_Net]","[Dashboard_Data].[Level_1].["&A$1&"]")

kindly help me to overcome this issue.

Punith GP
  • 690
  • 4
  • 11
  • 33

1 Answers1

2

First create a =CUBESET function in cell A1. 

=CUBESET("ThisWorkbookDataModel","Filter([Dashboard_Data].[Level_1].[Level_1].Members, Right([Dashboard_Data].[Level_1].CurrentMember.Name, 6)=""_India"")")

Basically that is a language called MDX and the expression before "" double quote escaping is:

Filter([Dashboard_Data].[Level_1].[Level_1].Members, Right([Dashboard_Data].[Level_1].CurrentMember.Name, 6)="_India")

Then reference it in your =CUBEVALUE formula:

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Bookings_Net]",$A$1)
GregGalloway
  • 11,355
  • 3
  • 16
  • 47