Cubevalue filtering with wildcardsd in Excel

Question

Hi,

I converted a pivot table to formulas with the OLAP Tools > Convert to Formulas option and now I want to filter down the cubevalues to only count if a predefined value that lives in a cell exists in the Rule field. i.e "RULE1" or "RULE66". (table below)

I've tried creating a cubeset with wildcards and with the filter function but nothing works. I could really use some help!

EXAMPLE OF THE CUBEVALUE FORMULA

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Count of TransIds]","[Table1].[Rule].&[RULE66|RULE42|RULE4|RULE67|RULE50|]",{"[Table1].[Status].&[B]","[Table1].[Date].&[2023-05-24T00:00:00]")

I'd like to be able to create something like this:

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Count of TransIds]","[Table1].[Rule].&[*** A1 ***]",{"[Table1].[Status].&[B]","[Table1].[Date].&[2023-05-24T00:00:00]")

Where A1 = RULE66 or RULE4 or any of the rules that are part of the Rule Field

Thanks in advance for your help

Laura

Answer 1

Laura (probably too late...)

#1 Your below formula doesn't work - a closing brace is missing:

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Count of TransIds]",
   "[Table1].[Rule].&[RULE66|RULE42|RULE4|RULE67|RULE50|]",
   {"[Table1].[Status].&[B]","[Table1].[Date].&[2023-05-24T00:00:00]"
)

#2 To reference a cell (A1 below) inside a similar formula, syntax is:

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Count of TransIds]",
  "[Table1].[Rule].&[" & A1 & "]", ...
)

#3 Wildcards are not possible as you expect. To a certain extent this can be achieved with the appropriate MDX formula - with CUBESET. Then your formula becomes something like:

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Count of TransIds]",
  CUBESET(...)
)

Answer 2

Similar case: Excel cubeformulas (CUBEVALUE) and wildcard strings? Maybe using INSTR?

Reference: VBA functions in MDX and DAX (it seems we're limited to those VBA functions when dealing with the Excel Data Model)

Example:

Formula (in C14):

=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Count of TransIds]",
  {"[Table1].[Status].&[B]"},
  CUBESET("ThisWorkbookDataModel",
    "{
      Filter([Table1].[Rule].[Rule].Members,
        Instr([Table1].[Rule].MemberValue, '" & C13 & "') <> 0
      )
    }"
  )
)

Corresponding sample available here

Answer 3

BTW, if you have a scenario like below and don't want to count RULE4xyz when the filtering criteria is RULE4:

Formula is C14 becomes (concat. the | to value in C13):

=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Count of TransIds]",
  {"[Table1].[Status].&[B]"},
  CUBESET("ThisWorkbookDataModel",
    "{
      Filter([Table1].[Rule].[Rule].Members,
        Instr([Table1].[Rule].MemberValue, '" & C13 & "|') <> 0
      )
    }"
  )
)