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

[jj1]

Hi 

Table of data in query has multiple columns

job id column is unique but version # column shows multiple values per job id-i need only the highest version or max value of version for each job id 

column1 job id 1-100

column 2 version assoicated with each job id-multiple versions- need only max version within each job id

so job id 1 may have version 1-5(need only data for version 5(max version value)

job id 2  may have version 1-3(need only data for version 3(max version value)

[dufoq3]

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

[jj1]

hi this formula did not keep the original job id that i need- and it now created multiple rows more of that job id than before so it did solve the need to pull max version and associate the total value per paper type within each unique job id i lost the key date i need to keep which is job id should show as 6370, 6365 etc not job id 1,2,3

[dufoq3]

Look at the screenshot I've uploaded in my previous post. Could you provide expected resut based on my sample data but maybe it is not necessary. I think you don't know how to use my query - read note below my post please.

[shafiz_p]

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!!

[jj1]

thanks your output screenshot  looks correct and is exactly what i need but maybe i am missing a step as i copy the dax formula in a new custom column   i get this error (see screenshot) so it seems i only need to add a custom column within power query and your output of final result is exactly what i need yet seems the dax formaul i am using from you is giving me an error so can you clarify the steps- do i only copy and paste your dax into a new custom column or do i need to do other steps too to  achieve your output view ?

[shafiz_p]

@jj1  ... Do not need to create custom column. Open power query, go to home tab, click advance editor and paste code. See the image below:

Paste code here. Change column name accordingly. I don't know your current steps in power query. Keep all the preceding steps and place all from Grouped steps at the end of your current steps.

If you face any problem, please let me know.

Hope this helps!!

[jj1]

what do i place here? once i cope your formula? which name is it seeking?

[shafiz_p]

My source is excel file and data table name is "Paper Data".  Replace "Paper Data" with your original table name. then place the remaining codes. You can see in the picture, here is only 5 column name. If you have more column, then add them within curly brackets {}.

[jj1]

nevermind its my source-i had

[shafiz_p]

Excel table name not be possible to space separated. Please check your excel table name. Please delete all and start fresh and provide your code. If possible, provide your file.

[jj1]

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

[jj1]

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

[Asmaa_Bedawi]

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.