CUBESET in Excel referencing multiple dimensions

[JOAP]

Hi,

I have tried for a long time now to find anyone showing the CUBESET function in Excel and Power BI that can include multiple dimensions from different tables? I've seen examples of using EXISTS and NONEMPTY, but in my case I fail to get anything to work except for a simple set like:

=CUBESET(MyCube;"[Customer].[Customer_Name].members";"Customers";2;"[Measures].[Amount_YTD]")

I also need to insert several other dimensions in my CUBESET in order to later use CUBERANKEDMEMBER

One more simple field would actually need to come from the same [Customer] table:

[Customer].[Customer_Group_Code].[CG01]

I for example also need a filter from another table:

[Company_Group].[Company_Code].[2053]

etc.....

BR Johan

Starting to think that this is not possible 😥

[v-juanli-msft]

Hi @JOAP 

Based on my understanding, ignoring the connection and caption arguments, what this formula means is: 

“Return all members from the [Customer_Name] column in the [Customer] table, sorted descending by the Amount_YTD measure.”

To apply multiple criteria to the Cubeset Expression, here is a similar thread.

https://stackoverflow.com/questions/49418708/cubeset-function-in-excel-with-multiple-criteria

Besides, it could be done using DAX like:

https://docs.microsoft.com/en-us/dax/rankx-function-dax

for example

measure=rankx(filter(allselected(tablename),tablename[column1]="ds"),[measure sorted by],ASC,,DENSE).

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JOAP]

Many thanks for your valuable tips. I've seen the stackoverflow page before but couldn't get that to work for me and to solve it using my underlying Power BI Desktop Pro Model (pbix) would be an alternative but this time I need the solution directly build in Excel by using the CUBESET function.

After some more testing today and by looking into TopCount for SQL Server 2019 I was surprised when I got the below to actually work in Excel:

=CUBESET(MyCube;"{[Actualities].[Actuality].[All].[AC]*([Company_Group].[Company_Code].[All].[2053]*[Customer].[Customer_Name].members)}";"CustSet";2;"[Measures].[Amount_YTD]")

Using * and () around company_code and customer_name, somehow solved it but I'm unsure why?

Trying to also add a 3rd dimension I have like: [Currency].&[LC] failed when trying to place it in with * and some testing with ()s.

/Johan

[JOAP]

Sorry I of course meant to add a 4th dimension for [Currency] that failed 😅

[edhans]

I would consider asking that question in the Excel Tech Community. Someone here might have the answer, but the people over there spend a LOT of time in Excel. I know only enough of the CUBE() functions that I try to say away from them. 😁