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

Question

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

MoazRub · Answer 1 · 2019-12-16T11:34:07.760

1

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

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

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]

edited Dec 16 '19 at 11:34

answered Dec 13 '19 at 16:10

MoazRub

2,881
2
10
20

What is RANK and ORDER doing that doesn’t just like all combinations but restricts it to the top for each category? Also, what does “currentmember” represent! – obizues Dec 13 '19 at 22:53
Or is it this line that limits less than 2 subcategories? “<2?” – obizues Dec 13 '19 at 22:55
This line limits the result to the highest value. Change it to 3 and see the effect – MoazRub Dec 13 '19 at 22:59
I changed my code to this, but it made it started to show huge numbers. Code: 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] ) Picture: https://i.imgur.com/9yEtyiH.png – obizues Dec 14 '19 at 03:03
I additionally edited the original post with your suggested changes, my code, and a screenshot of the results for easy reading. – obizues Dec 14 '19 at 03:17

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

1 Answers1