CODEWITHSUNDEEP
sqlmaxssasmdx
obizues
obizues

Reputation: 1473

How do I select the max value of multiple rows with the same product name with MDX SQL?

EDITED With RANK/ORDER Suggested changes below:

Currently I'm returning the sum of the Prices for all Suppliers that sell the product.

What I want is to return the Product Name, and Price for the row that has the max highest price for the rows with the same product for each product.

Code:

--Find the maximum price for each product offered in Madison
WITH MEMBER [Measures].[Max Value] AS MAX([Measures].[Price]) 

SELECT NON EMPTY {
    [Measures].[Max Value]} ON 0,
NON EMPTY { 
    ([Tb Product].[Name].[Name] ) }  ON ROWS 
FROM [DS715]
WHERE ([Tb Supplier].[City].&[Madison])

EDIT:

WITH MEMBER [Measures].[Max Price RANK] AS 
RANK( ([Tb Product].[Name].currentmember), 
ORDER( ([Tb Product].[Name].currentmember), [Measures].[Price - Tb Transactions], BDESC) ) 

SELECT 
NON EMPTY { 
    [Measures].[Price - Tb Transactions] } ON COLUMNS, 
NON EMPTY { 
    filter([Tb Product].[Name].[Name], [Measures].[Max Price RANK] <2 )} ON ROWS 
FROM [DS715] 
WHERE ( [Tb Supplier].[City].&[Madison] )

Now only one row for each, but with very large numbers

enter image description here

Upvotes: 0

Views: 514

Answers (1)

MoazRub
MoazRub

Reputation: 2911

RANK function is what you need to use. In the example below I am listing all the Product categories with their subcategories and their internat sales

Query 1 

select 
{
[Measures].[Internet Sales Amount]
}
on columns,
non empty
([Product].[Category].[Category],[Product].[Subcategory].[Subcategory])
on rows 
from 
[Adventure Works]

Result Result 1

Now Lets modify the query to restrict to the member with max Internet Sale

 WITH 
MEMBER [Measures].[Internet Sales Amount Rank] AS
RANK( ([Product].[Category].currentmember,[Product].[Subcategory].CurrentMember),
ORDER( ([Product].[Category].currentmember,[Product].[Subcategory].[Subcategory].Members) , [Measures].[Internet Sales Amount], BDESC)
) 

select 
non empty
[Measures].[Internet Sales Amount]
on columns,
non empty 
([Product].[Category].[Category],filter([Product].[Subcategory].[Subcategory],[Measures].[Internet Sales Amount Rank]<2))
on rows 
from [Adventure Works]

Result

enter image description here

Edit

You left the city out that is why rank is not working. make the following changes

WITH MEMBER [Measures].[Max Price RANK] AS RANK( ([Tb Product].[Name].currentmember,[Tb Supplier].[City].currentmember), ORDER( ([Tb Product].[Name].currentmember,[Tb Supplier].[City].[City].members), [Measures].[Price - Tb Transactions], BDESC) )

SELECT NON EMPTY { [Measures].[Price - Tb Transactions] } ON COLUMNS, NON EMPTY { ([Tb Product].[Name].[Name],filter([Tb Supplier].[City].[City], [Measures].[Max Price RANK] <2 )} ON ROWS FROM [DS715]

Upvotes: 1

Related Questions