Forum Discussion
Find duplicate column using max value fom another column and create a new column coded as 0 or 1
Hi, Need to create a new column which finds the duplicates and gives a one to those duplicates which has the highest value in a different column. If 2 duplicates have the highest value give 1 only t...
Hi Anonymous ,
Create a column as below.
Column = var _max = CALCULATE(MAX('Table'[Sales]),FILTER('Table','Table'[cust]=EARLIER('Table'[cust])&&'Table'[Report_month]=EARLIER('Table'[Report_month]))) return IF('Table'[Sales]=_max,1,0)Best Regards,
Jay
lbendlin
Super User
Super Userlet
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddCxDoIwEAbgV2mYbdJWfQCig4uSeAMDIYbUiw4oRIqJSR9exAYoPYabvvx3fy7LIt02xp6xrl7m8qie5m6hKLGxgCVqg9coX2URpLFla8nj9saVZJaJbiSbmOCA9YJJnmhDWhc7VW+aJN+j/pPqmaTNmNqlhx8di0+w0NFQXy6TGG85osr3RJd3qaHhhBIA75YKyEuJCdH/dSmvoaBpO0vR5ecNw4XB5/Mv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sales", Int64.Type}, {"Selected", Int64.Type}, {"Report_month", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=> if Table.Sort(Table.SelectRows(#"Changed Type",each k[cust]=[cust] and k[Report_month]=[Report_month]),{{"Sales", Order.Descending}})[Sales]{0} = k[Sales] then 1 else 0)
in
#"Added Custom"