Forum Discussion
Cube functions
Good day/night all!
Nervous but excited to be here as I am a newbie to excel but trying to get into the mind of excel day by day. I've just recently gotten into making data models and now I want to explore getting information out the cube without the use of pivot tables. This cube was not made by me. I'm using it to practice the cube functions because I intend to use them in a data model that I plan to make very soon. I would want to be able to make an input cell apart of a cuberankmember function argument. In the excel file that's shown in pictures below, here's what I'm trying to accomplish. Under the column "Customer_ID", I'd like to enter the id number and have the customers full name show up under the "Full_Name" column. Now if I do change the number currently under customer_id, the name does change, but it's being sorted in alphabetical order rather than rank or I.D number. For example, number 5 renders Aaron Conklin because alphabetically, it's the fifth in the cubset of full names in cell G3. I want I.D. # 5 to give me back the customer's full name that corresponds, which should be Maya Gutierrez. Keep in mind the rank number is equal to the id number. If anyone has the solution to bypass the alphabetical order results showing up, pleassssse pass on the knowledge. Also, I'm still learning terminologies of excel. I'm using the latest 365 personal edition 64-bit on windows 10. ThShowing function cubesetShowing function cuberankmember
9 Replies
Let me illustrate on simple model where Id is not necessary sequential numbers.
Source table loaded to data model is sorted by Names
We may create 2 sets, one for IDs only, by default it will be sorted by ID:s
=CUBESET("ThisWorkbookDataModel", " [Table1].[ID].[ID].MEMBERS", "IDs" )Another one includes IDs and Names, will be sorted by Ids and returns Names
=CUBESET("ThisWorkbookDataModel", "( [Table1].[ID].[ID].MEMBERS, [Table1].[Name].[Name].MEMBERS )", "Table" )To return ID
=CUBERANKEDMEMBER("ThisWorkbookDataModel", $F$2, E5)To return Name
=CUBERANKEDMEMBER("ThisWorkbookDataModel", $G$2, E5)- Thank you so much for the quick response. I'm definitely going to try this out an let you know. Thing is, I did make 2 separate sets one for I.Ds and one for the names as I researched (I did a good bit and there's not much on cube functions) and found that I could make a set out of 2 members from the same dimension, but that was an MDX video and I felt like it may not work and I felt like it may be far fetched. I couldn't figure out how to get join the sets. So thank you sooo much for this. It gave me confidence to know that with guidance and drive, I'm getting on the right track of thinking like yourself and other excel gurus. I'll get back to you with the results. Smiles!
Unfortunately don't remember any guidance, only samples in different places. And yes, if go deeper that's all about MDX. You may use practically all MDX functions and expressions within cube formulas.
