• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cube formulas without creating a Pivot table

I played with the datamodel from:
http://chandoo.org/wp/2013/07/01/in...013-data-model-relationships/#comment-1456901

Creating tables and relationships between them. It also works in Excel 2016.
I can even create Cube Formulas on the Data model after I've inserted the pivot table.

Just for the fun of it, I tried to see if I could do Cube Formulas without creating the pivot table in advance. I can define Cube members, but it seems as if the measure part is playing tricks on me.

I can't get a Cube Value for Chocolates sold to Male customers.
With the Pivot created the formula looks like this (and works fine)
=CUBEVALUE("ThisWorkbookDataModel";"[Customer].[Gender].&[Male]";"[Product].[Category].&[Chocolates]";"[Measures].[Sum of Quantity]")

I've uploaded a file for you to look in, the problem cells are marked yellow.

Does anyone know how I can solve this, or am I asking the impossible?
 

Attachments

  • Relationship Excel tables.xlsx
    515 KB · Views: 5
Well... without measure being defined in data model you can't use it in CUBE functions.

If in data model, measure is defined using DAX like below image...

upload_2017-6-8_9-26-24.png

Then you'd be able to access it.
upload_2017-6-8_9-27-18.png

However, this method requires PowerPivot add-in. Without it, to access measure, you'd first need to create Pivot Table and using OLAP tool, convert it into formula.
 
Back
Top