Get related value to the max value of a measure column

[Anonymous]

Hey there!

I'm currently displaying multiple columns, all calculated by measures, in a table visual:

Product Name	Total Sales	Total Sales MoM	Total Sessions	Total Sessions MoM
Product1	3000	50%	200	42%
Product2	4500	-6%	200	10%

Those measures are stored in a measure table.

On a card visual for each MoM column, I want to display the MAX value of these columns and the respective "Product Name" as a description.

I already got a solution for showing the maximum number, although I'm not sure if this is the leanest solution:

MAXSalesByProductName = 

MAXX(

    KEEPFILTERS(VALUES('DimProduct'[Product Name])),

    CALCULATE('MeasureTable'[Sales_TotalSales MoM%])

)

I can't think of a solution to get the product name, as CALCULATE does not accept measures as filters and the filter function does not accept the MeasureTable as it is empty:

First attempt with CALCULATE:

VAR __MAX_SALES = 'MeasureTable'[MAXSalesByProductName]

RETURN

CALCULATE(

FIRSTNONBLANK('DimProduct'[Product Name], 'DimProduct'[Product Name]),

'MeasureTable'[Sales_TotalSales MoM%] = __MAX_SALES

)

Error: A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Second attempt with FILTER:

VAR __MAX_SALES = 'MeasureTable'[MAXSalesByProductName]

RETURN

CALCULATE(

FIRSTNONBLANK('DimProduct'[Product Name], 'DimProduct'[Product Name]),

FILTER('MeasureTable', __MAX_SALES=[Sales_TotalSales MoM%])

)

Error: Table 'MeasureTable' cannot be used because it does not have any columns.

So how to achieve this with this setup?

[PaulOlding · Accepted solution]

Hi @Anonymous 

The general pattern you can use for this is

Max Product Name = 
MAXX(
    TOPN(1, DimProduct, [Total Sales], DESC),
    DimProduct[Product Name]
)

Here I'm using a [Total Sales] measure to get the top product but you can use any measure.

Similarly, I'm returning the Product Name here but you can change that to any dimension attribute/column you like.

[amitchandak]

@Anonymous , You can use TOP 1

Top 1 = calculate(Max('DimProduct'[Product Name]),TOPN(1,allselected('DimProduct'[Product Name]),[Sales_TotalSales MoM%],DESC), values('DimProduct'[Product Name]))

In the bold max , you can have measure if you want top1 of any measure based on TOPN function