Reader Interactions


  1. Ben

    Hello Mynda,

    Thank you for the great video and clear instructions. I was trying to follow along with my own data set that was built in Power BI and exported to analyze in Excel. I added some shops to the columns and their sales to the values but when I use the OLAP tool to convert to formulas I keep getting this error.

    “Fields in the Values area prevent conversion to formulas because they are defined in Excel, not on the server or service. To remove fields from the Values area and retry converting to formulas, select OK.”

    I don’t understand why it keeps throwing this error when it was built on the service. Do you know how to fix this?


    • Mynda Treacy

      I’d say it has something to do with this “I added some shops to the columns and their sales to the values”.

  2. Andrew Howe

    Well this was nice, with a nasty sting in the tail for me at least

    =CUBEVALUE(“ThisWorkbookDataModel”,CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[“&”Sum of Amount”&”]”))
    returns 68,566

    So I thought I could generalize with Count, Count Distinct and apply to other fields, without going through the pivot table route, given that the data is already in the data model. But the following all return #N/A:

    =CUBEVALUE(“ThisWorkbookDataModel”,CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[“&”Count of Amount”&”]”))

    =CUBEVALUE(“ThisWorkbookDataModel”,CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[“&”Count of Sub-category”&”]”))

    =CUBEVALUE(“ThisWorkbookDataModel”,CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[“&”Count Distinct of Sub-category”&”]”))

    The technique would be incredibly useful as a quick investigation of data within data models if the above worked.

    • Mynda Treacy

      Hi Andrew,

      Those measures are implicit. You need to create a PivotTable with those implicit measures before the CUBE functions can see them in the model. You can then delete the PivotTable. Alternatively, and better is to write the measures explicitly in the model and then reference them with the CUBE functions.


  3. Mustapha

    Hi Mynda,

    How do you add a slicer to the CUBEVALUE formulas for a field that is in a linked table in the data model? In your file for example how would I add the Category as a slicer in to the formula? I can add sub-category but get #VALUE!.

    • Mynda Treacy

      Insert the Slicer, then right-click and edit the properties to get the Slicer formula name. Insert this in one of the member expression arguments, as you can see in my example.

      • baxbax

        Hi Mynda,

        That works fine for any field that is in the Data table but not if the field is in the Categories table even though they are linked in the Data Model. As mentioned I tried it with the Category field. The slicer formula name is “Slicer_Category” but if you add this in to the formulas it gives #VALUE!.

        • Mynda Treacy

          I suspect you have conflicting filter context. If you use this formula based on my file references it works:

          =CUBEVALUE("ThisWorkbookDataModel",$B$5,P$6,Slicer_Date__Year1, Slicer_Category)


  4. Tom

    Hi Mynda,

    Thanks for all you do and all the fantastic info you post/share. Sorry if this is a “newb” question, but how to you get the “OLAP tools” button to show and not just be grayed-out? I tried to convert a pivot table I had previously created and couldn’t. When I web-searched for an answer all the answers that came up said that this tool was only for pivot tables which were connected to an OLAP source (i.e. an external SQL source). It this true, and if not, how can I use this tool for my pivot table?

    Thanks for your patience. Cheers, Tom.

    • Mynda Treacy

      Hi Tom, the OLAP tools are only available for Power Pivot PivotTables. As shown in the video at the start, you have to check the ‘add to data model’ button when creating your PivotTable for this to work. Hope that clarifies things.

  5. Ashok Patel

    I have Excel 2013 and, in my pivot table, Analyze tools setting OLAP function is grayed out. How do I enable it? Your article does not say anything about this issue.

    • Mynda Treacy

      Hi Ashok, you need to load your data to Power Pivot for the OLAP tools to be available. Not all versions of Excel 2013 have Power Pivot. You can check the list of versions that do and don’t have Power Pivot here.

  6. Philip T

    Another excellent video, Mynda. thank you. I am puzzled by the case where there are imbedded spaces in the member-name. In your workbook, I pointed a CUBEVALUE function to “Dining Out” in column B.

    If you paste-values the CUBEMEMBER function as text, the CUBEVALUE function works fine. If you type in the text or, after pasting, edit it in anyway (delete a letter then put it back in), the CUBEVALUE returns #N/A. Wrapping the text in square brackets solves the problem but looks ugly. Strange.

  7. Paul Martin

    Whilst we are all trying to promote cool tips and tricks regarding Excel (and you do this better than most), I think we need to give balanced and honest feedback re the downsides of using certain functions and the CUBE functions DEFINITELY fit into this category. I saw this “old and scarcely used for a good reason” function trotted out again at the London Excel User Group in December. People lost their minds through excitement and I am thinking :

    i) Doesn’t scale at all (architecturally when used against SSAS Tabular or MD Cubes) at worst it performs one query PER CELL. You can guess how efficient this is ! There is an obvious reason why it says “getting data” in each CUBE function cell on certain reports.

    ii) It is very hard to maintain. Yes, convert to formulas is lovely on a new report / web demo and you simply cut and paste formulas as required. You now add 10 nominals to the GL next month and want to insert these into the report ? Uh, not so easy. Nothing can be data driven, so you’d better check EVERY CUBE function report EVERY month / period, as you’d better hope the data source doesn’t change !

    So if you get excited by the CUBE functionality, my advice is test WITHOUT rolling out and see if it performs as expected. Then and ONLY then inform your users.

    • Mynda Treacy

      Thanks for sharing your experiences, Paul! I agree, they have their downsides and are better suited to models that are more static in their structure. I like them for pulling in a few values here and there that don’t warrant a bulky PivotTable.

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *