Forum Discussion

Copper Contributor

Dec 18, 2021

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 t...

Formulas and Functions

office 365

training

SergeiBaklan

Diamond Contributor

Dec 18, 2021

ashmackey85

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)

cuberankmember sample.xlsx105 KB

ashmackey85

Copper Contributor

Dec 18, 2021

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!

SergeiBaklan
Diamond Contributor
Dec 18, 2021
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.
- ashmackey85
  Copper Contributor
  Dec 19, 2021
  SergeiBaklan
  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. A
  - SergeiBaklan
    Diamond Contributor
    Dec 19, 2021
    ashmackey85
    
    There are brackets as at left bottom here
    
    In your formula I see no brackets at all.