Forum Discussion
How do i find maximum values from a column with respect to other column data
Migrating bobj report to power bi. In bobj I have a calculation as = Max(IF([Replicate = 1] Then ([Result])) In ([Batch ID]) Sample Data Batch ID Result Value Replicate 817 2.93 1...
Hi Anonymous
If you want a Dax solution, you can add a calculated column with the formula:
max_by_id = CALCULATE(max('Table'[Result Value]), ALLEXCEPT('Table','Table'[Batch ID]))Pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi Anonymous
If you want the values to change according to the slicers / filters selections you cant use calculated columns and need to create measures.
The updated pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
lbendlin
Super User
Super Userlet
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjA0V9JRMtKzNAZShkqxOgghC1MQBREyMgSyjfUMjBCqwEJAjSYQVbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Batch ID" = _t, #"Result Value" = _t, #" Replicate" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Batch ID", Int64.Type}, {"Result Value", type number}, {" Replicate", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Batch ID"}, {{"Value", each List.Max([Result Value]), type nullable number}, {"Rows", each _, type table [Batch ID=nullable number, Result Value=nullable number, #" Replicate"=nullable number]}}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Result Value", " Replicate"}, {"Result Value", " Replicate"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Rows",{"Batch ID", "Result Value", " Replicate", "Value"})
in
#"Reordered Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".