Top 10 products with Lowest Reseller Order count of at least 400.

  • Article

 

This shows how to use Head, Order, Filter & TopCount Function.

 

Reseller Order Count

Classic Vest, S

514

Short-Sleeve Classic Jersey, XL

495

Hitch Rack - 4-Bike

468

AWC Logo Cap

460

Long-Sleeve Logo Jersey, L

451

Water Bottle - 30 oz.

444

AWC Logo Cap

442

Women's Tights, L

437

Long-Sleeve Logo Jersey, L

436

Short-Sleeve Classic Jersey, L

431

 

Well in AS there are different ways of achieving same thing:

We can get same result by

Select head(order(filter([Product].[Product Categories].[Product],[Measures].[Reseller Order Count]>400),[Measures].[Reseller Order Count],bdesc),10)
on 1 ,[Measures].[Reseller Order Count] on 0
from [Adventure Works]

or

select [Measures].[Reseller Order Count] on 0,
Topcount
(
Filter([Product].[Product].Members,[Measures].[Reseller Order Count] > 400)
,
10
,
[Measures].[Reseller Order Count]
) on 1
from [Adventure Works]

Comments

  • Anonymous
    December 19, 2013
    In 2nd mdx , you are using the filter on member, this will also show the All member. Output will be similar if  you change filter similar to 1 mdx i.e. Filter([Product].[Product Categories].[Product],[Measures].[Reseller Order Count] > 400)