MDX query for topX customer per many products optimization

[Jaromir Flidr]

Hi All
I'd like to find top customers for multiple products within a single query, e.g. topX customer for product A, topX for product B etc....

I was able to write an mdx query, which gets me the very top customer for each product, as follows

Code: Select all

with
member [EDataSet_B].[Top1] AS 'TOPCOUNT(TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER( {[ECustomer].[TR]}, ALL, RECURSIVE )},2),10,(EProduct.CurrentMember,[EDataSet_B].[CYF Book])).item(0).item(0).NAME'
select
{[EDataSet_B].Top1]} on columns,
{[EProduct].[MK_1BE],<more products there>,[EProduct].[Total Product]} on rows
from ESales

even though there is an easy way how to extend it to topX customers like

Code: Select all

with
member [EDataSet_B].[Top1] AS 'TOPCOUNT(TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER( {[ECustomer].[TR]}, ALL, RECURSIVE )},2),10,(EProduct.CurrentMember,[EDataSet_B].[CYF Book])).item(0).item(0).NAME'
member [EDataSet_B].[Top2] AS 'TOPCOUNT(TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER( {[ECustomer].[TR]}, ALL, RECURSIVE )},2),10,(EProduct.CurrentMember,[EDataSet_B].[CYF Book])).item(1).item(0).NAME'
<more members there>...
select
{[EDataSet_B].Top1],[EDataSet_B].Top2],<more members there>} on columns,
{[EProduct].[MK_1BE],<more products there>,[EProduct].[Total Product]} on rows
from ESales

I would like to optimize this to compute the top X customers only once per each product(row) and not duplicate it x times as depicted above .
Any ideas? As should be clear I'm just a very begginer with mdx ...

many thanks
Jaromir Flidr

[VRGultom]

Use Distinct to avoid duplicate tuples from a specified set.

Syntax:
Distinct(Set_Expression)