Cubevalue filtering with wildcardsd in Excel

Laura Cristina Mena Pizarro 0 Reputation points
2023-05-20T06:19:09.3766667+00:00

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

User's image

Thanks in advance for your help

Laura

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,681 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Lz._ 8,991 Reputation points
    2023-06-15T08:22:45.12+00:00

    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(...)
    )
    
    
    0 comments No comments

  2. Lz._ 8,991 Reputation points
    2023-06-18T07:13:11.3233333+00:00

    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:Sample

    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


  3. Lz._ 8,991 Reputation points
    2023-06-18T08:08:58.64+00:00

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

    Sample

    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
          )
        }"
      )
    )
    
    0 comments No comments