Cube functions

Occasional Contributor

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. Thexcel-pic.pngShowing function cubesetShowing function cubesetShowing function cuberankmemberShowing function cuberankmember

9 Replies

@ashmackey85 

Let me illustrate on simple model where Id is not necessary sequential numbers.

image.png

Source table loaded to data model is sorted by Names

image.png

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!

@ashmackey85 

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.

@Sergei Baklan 

Unfortunately the second function with the 2 sets isn't working for me. I'm getting a n/A error reading. I don't know what . I did actually try this before I made the post but in a way suggested in a mdx video on Youtube using {} to encase the joined members but that didn't work which brought here. But I'm sure we're both onto something, but I am blank. ANuExcel.png

@ashmackey85 

There are brackets as at left bottom here

image.png

In your formula I see no brackets at all.

@Sergei Baklan 

I saw the difference in what I missed. But even when I tried switching up the numbers in the file you sent the numbers didn't always show up correctly. But after researching some more videos, I noticed a trend. They all had a [measure] as a the "sort by". So I made a simple MIN( function of the ID numbers column and it now works once I choose ascending order as well. To anyone that may need to use this as reference, the numbers would have to in perfect order without skipping any numbers in between because if you don't the next number will screw everything up. For e.g. if you you were to have numbers from 1 to 10 and you miss out the 2 and the 4,  number 3 will come in as the 2nd smallest number and number 5 will be the 3rd smallest number. 

Also you will see the cubeset count one of the photos highlighted in blue w/ white text which is of course the correct amount. However, on another note, the number highlighted in brown tan w/ white text is where I get an N/a reading as if that's the limit of the set. Not sure if it's a bug in excel or if its from the fact the sample data model I used has several flaws unnoticed to me at this time

. Now I'm trying to see how I can get some kind of MATCH( function or something that can be nested the cube function. I'll try cubememberproperty since there's only 7 of themcube1.pngcube2.png. If you or anyone reading this knows a function that's compatible please make mention. Will try any and post results. Thank you  

Happy new year to everyone! May you truly "excel" in everything you set out to do. To anyone on a bit of a quest trying to uncover and understand the cube functions as I am, I hope my findings be of a saves you a bit of a headache. I am very new to excel so I am in no way or form, an expert. But I am soo fascinated with the software. I'm all ears and eyes to anyone who may have pointers or anything of the sort. Sometimes they correct my problems or spark a thought that enables me to correct the issues myself. With that said, this is just an update to this thread/ discussion. In my last post, I uploaded some screenshots of a page out of a workbook connected to a data model I made from a sample used to teach data modeling, power queries and power pivot, and so on. To cut to the chase, I just realized why I was getting error readings when I tried to have names pulled from the cube, matching the I.D. numbers. So, I have 10282 UNIQUE id numbers. However, the sample recycled 100 names meaning that there are only 10182 UNIQUE in a bunch of 10282. Cubeset function apparently only counts the first instance of a name rather than consider, and will only give you a set of UNIQUE or distinct results. I can say that it's strange, or if it's a bug or anything and I'm not sure how else to correct the issue other than to change the text string so that it's not identical, perhaps ending the with a trailing space or two. hope this helps.

@ashmackey85 

That will be better if you could attach sample file. Build data model based on description of some particular situation, make a guess which formulas are used and what is expected - is quite time consuming and not reliable.

@Sergei Baklan Here's the file itself attached below for your exploration. or anyone else. As I initially mentioned, I had issues getting information to show up correctly from the cube itself onto a spreadsheet. At first I was having issues with the cubeset function simply because it needed a "measure" which I had to figure out. Then still ran into issues with the cubeset of names. The cubeset count was 100 off. I couldn't understand why until it hit me to perform a distinct count of the names column. That is when I found my out what the issue was. I had duplicate names and excel only recognized the first occurrences of each name. I removed the duplicates. I was finally able to get the results I was hoping for. I wanted to be able to enter into a cell, the I.D number and be able to have the name associated with the id number show up. I was also able to get rid of "All" showing up when I entered id# 1. I'm not so advanced some I'm unable to add the formulas in a scroll bar like you did in your demonstrations which I found to be very neat.