How do i find maximum values from a column with respect to other column data

[harsha_9640]

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
817	2.85	2
821	3.02	1
821	2.94	2

Expected result

Bach ID	Result Value	Replicate	Value
817	2.93	1	2.93
817	2.85	2	2.93
821	3.02	1	3.02
821	2.94	2	3.02

@PowerBI 

@lbendlin 

@amitchandak 

@Ritaf1983 

@Idrissshatila 

@OwenAuger 

[Ritaf1983 · Accepted solution]

Hi @harsha_9640 

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

[Ritaf1983 · Accepted solution]

Hi @harsha_9640 

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

[harsha_9640]

Sample Data

Batch ID	Result Value	Replicate
817	2.93	1
817	2.85	2
821	3.02	1
821	2.94	2

Expected result

Bach ID	Result Value	Replicate	Max_Value	Min_Value	Range
817	2.93	1	2.93	2.85	0.08
817	2.85	2	2.93	2.85	0.08
821	3.02	1	3.02	2.94	0.08
821	2.94	2	3.02	2.94	0.08

@Rita Te approach is correct but the result is not expected for my data. I tink you didn't use the replicate filter. So, when Replicate is 1 the Max_value should be 2.93 and 3.02 and when it is 2 the Min_Value should be 2.85 and 2.94. And we need to calculate the range i,e Range =  Max_value - Min_Value. Please suggest this approach of the dax.

[Ritaf1983 · Accepted solution]

Hi @harsha_9640 

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

[harsha_9640]

Thanks for the solution. That worked.

[Ritaf1983]

Happy to help.

[lbendlin]

let
    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".