cancel
Showing results for
Did you mean:

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

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

 Name Order Amount A 1 100 A 2 50 B 1 25 B 2 100 B 3 200 C 1 1000 C 2 500

Table2

 Name Amount A 50 B 200 C 500
1 ACCEPTED SOLUTION
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] )

Please also check the link down below, which is the sample PBIX file.

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.

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] )

Please also check the link down below, which is the sample PBIX file.

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors