dax formula to extract highest value in one column and associate it with value in another column

first transform data.

• Sort the Data:

  • Sort Job ID ascending.
  • Sort Version descending.

• Remove Duplicates:

  • Select Job ID column.
  • Go to Home > Remove Rows > Remove Duplicates.

hi asmaa-thanks but this did not work- i need to mention another item in tha a job id can have multiple paper type associated with it- so 

job id 1 can have 2 distinct rows

job id 1 version 1-5 paper type a

job id 1 version 1-3 paper type b

so goal is need for each unique job id- for each distinct paper type tied to that job id-the total output per paper type within each unique job id- and the issue is each paper type has multiple versions and only need to use max or highest version for each unique paper type for that job id so need some kind of calculate max version table and then filter apply to job id table

so this is the issue- need the max version only within each unique job id for each paper type so some kind of max from version column and filter to unqiue job id formula-in the examples only want the yellow rows for each unique job id(notice it is max version # ) for each

Hi jj1 ,
Please use below code in power query to keep only rows with maximum version for each ID. 

let
Source = Excel.CurrentWorkbook(){[Name="PaperData"]}[Content],
TypeChanged = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Version", Int64.Type}, {"Active", type logical}, {"Paper_Item_No", type text}, {"Total_Paper_Consumption", Int64.Type}, {"Paper_Description", type text}}),

Grouped = Table.Group(TypeChanged, {"ID"}, {"MaxVersion", each List.Max([Version]), type nullable number}),

Joined = Table.NestedJoin(TypeChanged, {"ID", "Version"}, Grouped, {"ID","MaxVersion"}, "NewColumn"),

ExpandedNewColumn = Table.ExpandTableColumn(Joined, "NewColumn", {"MaxVersion"}, {"MaxVersion"}),
FilteredRows = Table.SelectRows(ExpandedNewColumn, each ([MaxVersion] <> null)),
RemovedMaxVersion = Table.RemoveColumns(FilteredRows,{"MaxVersion"})

in
RemovedMaxVersion

Result :

Hope this helps!!

If this solved your problem, please accept it  as a solution!!

Hi jj1,

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgWADpVgdCNcIhBFcYxCGcI2gik0QXBA2ReWaQbjGUMXmqFwLVK4lkBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Version = _t, Value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Version", Int64.Type}, {"Value", type number}}),
    GroupedRows = Table.Group(ChangedType, {"ID"}, {{"All", each Table.SelectRows(_, (x)=> x[Version] = List.Max([Version])), type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined