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 |
| 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 |
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
6 Replies
- lbendlin
Super User
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".
- Ritaf1983
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
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
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
