(ADD COLUMN) MAX VALUE MULTIPLE CONDITIONS

[LilianYip]

Hi, would need help on creating "Add Column"  for the current senario.

To choose the highest Delivery charge if the Clinic Name delivered by same driver in the same day.

Posting_Date	Sales_Invoice_No	Clinic Name	Driver	Delivery Charge	**Desired Delivery Charge	Note
1/4/2024	MISCDEL-8323	Clinic A	Driver A	5	5
1/4/2024	SI-24-031752	Clinic B	Driver A	5
1/4/2024	SI-24-031768	Clinic C	Driver B	5	5
1/4/2024	SI-24-031774	Clinic B	Driver A	10	10	** Choose the highest fee in the date for same clinic
1/4/2024	SI-24-031829	Clinic D	Driver B	10	10
2/4/2024	SI-24-031864	Clinic E	Driver B	5	5
2/4/2024	SI-24-031893	Clinic C	Driver B	10	10
2/4/2024	SI-24-031895	Clinic F	Driver A	5	5	** Only Count 1 trip
2/4/2024	SI-24-031923	Clinic F	Driver A	5

[v-zhengdxu-msft]

Hi @LilianYip 

Thanks for the reply from @lbendlin , please allow me to provide another insight:

I add a calculated column:

Desired Charge =
IF (
    'Table'[Delivery Charge]
        = CALCULATE (
            MAX ( 'Table'[Delivery Charge] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Posting_Date] = EARLIER ( 'Table'[Posting_Date] )
                    && 'Table'[Clinic Name] = EARLIER ( 'Table'[Clinic Name] )
            )
        ),
    'Table'[Delivery Charge]
)

 The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LilianYip]

Hi Zhengdong,

Thanks for the reply. But the outcome not exactly archieve. For multiple orders delivered to same clinic within same day by same driver will consider 1 trip.

So desired deliver fee to clinic F on 2024-02-04 only $5 and not showing $5 twice.

[v-zhengdxu-msft · Accepted solution]

Hi @LilianYip 

Please try this:

You can create a measure like this:

MEASURE =
VAR _currentdate =
    MAX ( 'Table'[Posting_Date] )
VAR _currentClinic =
    SELECTEDVALUE ( 'Table'[Clinic Name] )
RETURN
    CALCULATE (
        MAX ( 'Table'[Delivery Charge] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Clinic Name] = _currentClinic
                && 'Table'[Posting_Date] = _currentdate
        )
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LilianYip]

Hi Zheng Dong,

The formula still missed out the driver.

But thanks alot with the help.
Let me try to tweak the formula.

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ30TcyMDJR0lHy9Qx2dnH10bUwNjIGcp1zMvMykxUcgUyXosyy1CIw01QpVgdFV7CnrpGJroGxobmpEUKXE9G6zCwQupwRupzw6zI3wWmXoQFObRZGlghtLqiWQbUZYdNmhmSbKzY3YtVlaYzTZ/gsszRFaHPDFozYdFkiRxmmrlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Posting_Date = _t, Sales_Invoice_No = _t, #"Clinic Name" = _t, #"Driver " = _t, #"Delivery Charge" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Posting_Date", type date}, {"Sales_Invoice_No", type text}, {"Clinic Name", type text}, {"Driver ", type text}, {"Delivery Charge", type number}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type",each [Delivery Charge],each [Delivery Charge]+Number.Random()/10000,Replacer.ReplaceValue,{"Delivery Charge"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value1", {"Clinic Name", "Driver ", "Posting_Date"}, {{"Desired Delivery Charge", each List.Max([Delivery Charge]), type nullable number}, {"Rows", each _, type table [Posting_Date=nullable date, Sales_Invoice_No=nullable text, Clinic Name=nullable text, #"Driver "=nullable text, Delivery Charge=nullable number]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Sales_Invoice_No", "Delivery Charge"}, {"Sales_Invoice_No", "Delivery Charge"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Rows",each [Desired Delivery Charge],each if [Desired Delivery Charge]=[Delivery Charge] then [Desired Delivery Charge] else null,Replacer.ReplaceValue,{"Desired Delivery Charge"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Desired Delivery Charge", Int64.Type}, {"Delivery Charge", Int64.Type}})
in
    #"Changed Type1"

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". Once you examined the code, replace the Source step with your own source.