Reply
avatar user
Anonymous
Not applicable

Get related value to the max value of a measure column

Hey there!

 

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

Product NameTotal SalesTotal Sales MoMTotal SessionsTotal Sessions MoM
Product1300050%20042%
Product24500-6%20010%

 

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?
1 ACCEPTED SOLUTION
PaulOlding
Solution Sage
Solution Sage

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.

PaulOlding_0-1641297019954.png

 

View solution in original post

2 REPLIES 2
PaulOlding
Solution Sage
Solution Sage

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.

PaulOlding_0-1641297019954.png

 

amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)