How to use wildcard in Cubevalue formula in Excel?

Question

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.

Answer 1

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)