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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors