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

Lookupvalue based on Max criteria

Dear all

How can i lookup value in Table1 "Amount" column for with specific "Name" and Max "Order" and display in table 2 for each name

 

Table1

NameOrderAmount
A1100
A250
B125
B2100
B3200
C11000
C2500

 

Table2

NameAmount
A50
B200
C500
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

I hope I understood your requirement correctly, and please correct me if I am wrong.

 

please check the measures, and I come up with the new table like below picture.

 

Amount Value by Name and MaxOrder =
VAR maxorder =
MAXX ( Table1, Table1[Order] )
VAR amountbymaxorder =
CALCULATE ( SUM ( Table1[Amount] ), Table1[Order] = maxorder )
RETURN
amountbymaxorder
 
Amount Value by Name and MaxOrder V2 (total is fixed) =
VAR newtable =
SUMMARIZE (
Names,
Names[Name],
"@amountbymaxorder", [Amount Value by Name and MaxOrder]
)
RETURN
SUMX ( newtable, [@amountbymaxorder] )
 
 
 
Picture1.png
 
Please also check the link down below, which is the sample PBIX file.
 
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

I hope I understood your requirement correctly, and please correct me if I am wrong.

 

please check the measures, and I come up with the new table like below picture.

 

Amount Value by Name and MaxOrder =
VAR maxorder =
MAXX ( Table1, Table1[Order] )
VAR amountbymaxorder =
CALCULATE ( SUM ( Table1[Amount] ), Table1[Order] = maxorder )
RETURN
amountbymaxorder
 
Amount Value by Name and MaxOrder V2 (total is fixed) =
VAR newtable =
SUMMARIZE (
Names,
Names[Name],
"@amountbymaxorder", [Amount Value by Name and MaxOrder]
)
RETURN
SUMX ( newtable, [@amountbymaxorder] )
 
 
 
Picture1.png
 
Please also check the link down below, which is the sample PBIX file.
 
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.