Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors