Forum Discussion
CUBESET in Excel referencing multiple dimensions
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.
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
Sorry I of course meant to add a 4th dimension for [Currency] that failed 😅