Rank SSAS Cube data based on a filtered data set using MDX...

I explained how we can use MDX ranking in the below two posts.
MDX Rank and Advanced MDX Ranking.

But the queries given in those two posts will not work if you are filtering data using a dimension which is used for ranking and data is filtered using a sub cube. In such a case it will rank the data using the all the data set and filter it after that. If you filter it in the Where statement, Rank will work fine. But the issue is in this scenario you cannot use it, since the same dimension attribute is already used in the query.

To avoid this, we need to only use the filtered dimension members in the ranking calculation as well.
The sample query is as below. In that, ranking needs to be done for the selected stores and the used parameter is paramStores.

WITH
MEMBER [Measures].[RankSales] AS
RANK(([Store—Region_Store].CurrentMember,[Store—Region].CurrentMember,[Item—Department].CurrentMember),
ORDER(({STRTOSET(@paramStores)*[Store—Region].CurrentMember*[Item—Department].CurrentMember}),
[Measures].[Sales], BDESC))

SELECT {[Measures].[RankSales]} ON COLUMNS,
{STRTOSET(@paramStores)*[Item—Department].[Department].MEMBERS} ON ROWS
FROM  [Sales]

The output will be ranked only based on the available values in the paramStores parameter.