# CUBERANKEDMEMBER and CUBESET with Multiple Criteria



## ExcelNate (Aug 7, 2020)

This formula works in my spreadsheet:

=CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel","{([KPCalendar].[CalendarYear].&["&YEAR($C$247)&"],[KPCalendar].[CalendarMonth].&["&MONTH($C$247)&"], [FinancialSummary].[Location].&["&$A$247&"],*-*{[FinancialSummary].[Referring Group].[Internal]}, [FinancialSummary].*[Referring Physician]*.children)}","Needs Name",2,"[Measures].[YTD New Patients]"),ROW()-ROW($A$192))

But this formula returns #N/A:

=CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel","{([KPCalendar].[CalendarYear].&["&YEAR($C$247)&"],[KPCalendar].[CalendarMonth].&["&MONTH($C$247)&"], [FinancialSummary].[Location].&["&$A$247&"],*-*{[FinancialSummary].[Referring Group].[Internal]}, [FinancialSummary].*[Referring Group]*.children)}","Needs Name",2,"[Measures].[YTD New Patients]"),ROW()-ROW($A$192))

I think the difference between the two formulas is that I am trying to filter out [FinancialSummary].[Referring Group].[Internal] and the second formula also references [FinancialSummary].[Referring Group] a second time.

I have tried several different ways to adjust the syntax of the second formula to rank Referring Group by YTD New Patients while filtering out the Referring Group category Internal, but can't seem to get the syntax right.

I have created a simplified version of the spreadsheet here:



			http://mooresolutionsinc.com/downloads/CS.xlsx
		


There are several potential workarounds with GETPIVOTDATA, INDEX and MATCH, etc., but the actual spreadsheet has maybe 20 tabs and hundreds of CUBEVALUE functions.  There has to be a way to get CUBESET to reference the same column twice.

Any suggestions would be much appreciated.


----------

