SQL BI

My Experience - Troubleshooting SQL BI Stack

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

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

  • Comments 1

 

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
  • 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)

Page 1 of 1 (1 items)
Leave a Comment
  • Please add 1 and 5 and type the answer here:
  • Post