This argument either slices the data cube through certain members, or indicates a DAX Measure. Excel automatically creates it, and generally for me this is “ThisWorkbookDataModel”. This refers to the name of your Data Model. The syntax of a CUBEMEMBER formula consists of 3 arguments: CUBEMEMBER( connection, member_expression, caption) If the value does not exist, the formula will return #N/A. The formula verifies whether the specified member exists in the data model/cube and if it does it will return this member. It determines what part of the data cube your CUBEVALUE formula returns. The CUBEMEMBER function returns a member of the cube. So what’s the purpose of these Cube functions and how do they work? CUBEMEMBER Other Cube function s exist, yet to get started these two are the most important ones to master. The functions CUBEMEMBER and CUBEVALUE play a central role in retrieving data from the Data Model. Without adjustments, the CUBEVALUE formula can easily be copied down. It always references the Total Sales Measure, and the cube member to the left of it, referencing the day of the week. Notice that the CUBEVALUE formula is the same for each value. The numbers are CUBEVALUE formulas that in this example reference two CUBEMEMBER formulas.Yet it references all the values, instead of filtering a single day. The blue marked cell is a CUBEMEMBER formula that also references the column Day Name in the Calendar table.In this case the column Day Name from the Calendar table filtered on Friday. The green marked cells are CUBEMEMBER formulas that contain references to a single member of the data model.The orange marked cell is a CUBEMEMBER formula that contains a cell reference to the DAX Measure called Total Sales.The newly created cube functions are CUBEMEMBER() and CUBEVALUE(). The coloured cells on the right contain similar formulas as the coloured cells on the left.
To follow along you can use this example file. The easiest way to get started with Cube functions is by using a Pivot Table that uses the Data Model as its Data Source. Since I don’t want to bore you by too much theory, let’s finally get our hands dirty. They instead depend on the measures defined in DAX. Cube functions will never replace the DAX formula language. It is through this portal that the Excel user gains access to the Data Model data. You could say that Cube formulas are the portal between the Data Model and the Excel Worksheet. A measure defined in DAX, is one of the possible members that cube functions can reference. Cube functions on the other hand reference members and get values from the data cube. The definition of a measure resides in a so called Measure and is saved as part of the Data Model. The DAX language is a necessary component to perform calculations on your dataset. In fact, there’s a beautiful collaboration between DAX (measures) and Cube functinos. How is it that Cube functions retrieve data from the Data Model in the Excel Worksheet?“ There’s no need to worry. Now you may say: “ Hey I’ve invested all my time in learning DAX to do calculations on the Data Model. Cube functions can interact with the Data Model in Power Pivot. Yet Cube Functions in Excel are formulas that allow users to retrieve data from certain kinds of sources. And so traditional Excel formulas can’t retrieve the Data from the Data Model. That means that there are no cells available on the worksheet for traditional Excel formulas to reference.
Data residing in Power Pivot’s Data Model, is not directly visible in the worksheet.
0 kommentar(er)
