Filtering a cubeset function by date

[cmgish]

I have an Excel model with a single table for which I need to create a cubeset. I have a [Date] column and a [Team] column with many other columns of statistics. What I want to do is create a cubeset based on the date. My table is named Team_Stats, and looks like this:

I was hoping to be able to use the cubeset function to create one cubeset based on the date "11/4/2024", and another cubeset based on the date "11/6/2024". I have tried using the FILTER function nested within the CUBESET function, but I'm sure I'm getting the syntax wrong. Can anyone help? The goal of creating the cubesets is to use the CUBERANKEDMEMBER function to pull the top 3 teams or top 5 teams of any one statistic based on the date. Thanks to anyone that can help!
Chris

 

[RoryA]

You could use something like this (assumes a date in A2):

=LET(dt,TEXT(A2,"yyyy-mm-dd"),CUBESET("ThisWorkbookDataModel","([Team_Stats].[Date].&["&dt&"T00:00:00],[Team_Stats].[Team].children)","Cubeset for "&dt))

 

[cmgish]

Thanks so much RoryA! That formula did filter my cubeset the way I wanted it. However, for some reason I can't get my CUBERANKEDMEMBER function to work. Not really sure what I'm doing wrong. I copied the function you wrote and then added the parameters to sort the CUBESET in descending order, and then to sort by [Team_Stats].[FG%]. So now, the formula looks like this:

=LET(dt,TEXT(A2,"yyyy-mm-dd"),CUBESET("ThisWorkbookDataModel","([Team_Stats].[Date].&["&dt&"T00:00:00],[Team_Stats].[Team].children)","Cubeset for "&dt,2,"[Team_Stats].[FG%]"))

I then write my CUBEMEMBER function as:

=CUBEMEMBER("ThisWorkbookDataModel","[Team_Stats].[TRB]","Rebounds")

Now, my CUBERANKEDMEMBER function looks like this:

=CUBERANKEDMEMBER("ThisWorkbookDataModel",$Z$656,$AB658)

Z656 holds the CUBESET and AB658 holds the CUBEMEMBER function above. I do get a list of team names (which is what I'm going for), but the results are incorrect. It lists the team names in alphabetical order, so the cubeset is sorted by the team name rather than by the statistic I want

 

[RoryA]

I don't follow - why are you passing a cube member as the third argument to CUBERANKMEMBER? It should be a rank number. If you are trying to sort the results by different statistics, it doesn't make sense to sort the original cube set. You could simply get the set for the date:

=LET(dt,TEXT(A2,"yyyy-mm-dd"),CUBESET("ThisWorkbookDataModel","([Team_Stats].[Date].&["&dt&"T00:00:00],[Team_Stats].[Team].children)","Cubeset for "&dt)

then adjust that to sort by whatever metric you want:

=CUBESET("ThisWorkbookDataModel",Z656,2,"[Team_Stats].[FG%]")

or

=CUBESET("ThisWorkbookDataModel",Z656,2,"[Team_Stats].[TRB]")

and then use CUBERANKEDMEMBER on whichever of those sets you want.

 

[cmgish]

I don't follow - why are you passing a cube member as the third argument to CUBERANKMEMBER? It should be a rank number. If you are trying to sort the results by different statistics, it doesn't make sense to sort the original cube set. You could simply get the set for the date:

=LET(dt,TEXT(A2,"yyyy-mm-dd"),CUBESET("ThisWorkbookDataModel","([Team_Stats].[Date].&["&dt&"T00:00:00],[Team_Stats].[Team].children)","Cubeset for "&dt)

then adjust that to sort by whatever metric you want:

=CUBESET("ThisWorkbookDataModel",Z656,2,"[Team_Stats].[FG%]")

or

=CUBESET("ThisWorkbookDataModel",Z656,2,"[Team_Stats].[TRB]")

and then use CUBERANKEDMEMBER on whichever of those sets you want.

Okay, so I can get the CUBESET formula and CUBERANKEDMEMBER formula to produce results, but what I can't get it to do, is put the ranked members in the proper order. It's like the function is ignoring me when I tell it how to sort the cube set. So, I'm going to pick a date that has fewer games so the results should be clear.

I then, as you suggested, adjusted that CUBESET function to sort by my chosen metric, which is field goal percentage by using this formula:

=CUBESET("ThisWorkbookDataModel",$B$693,"Field Goal %",2,"[Team_Stats].[FG%]")

When I write the CUBERANKEDMEMBER function, the results should show (in descending order) 1. Auburn, 2. Arkansas, 3. LSU, 4. Louisiana-Monroe, 5. Lipscomb, 6. Vermont. Instead, it ranks the members (or "children") in alphabetical order by the team name

There is only 1 date is this cubeset because I am only selecting part of the Date column as the original cubeset. The idea is that I am trying to show the top X number of teams' field goal shooting percentage on a given date. The entire table has dates for the entire season in it, but I was hoping to be able to limit a cubeset to one specific date and rank the members' stats based on whatever date that is. I am clearly doing something wrong, and I'm not experienced enough to figure out exactly what that is. Is there a simpler way to do this and I'm just over-complicating it, or am I just screwing this up somehow?

 

[RoryA]

Sorry for the delay - work got in the way.

You need to use a measure for the sorting, not a column in the source data.