Checking if value in one column matches value in another column

[Renee_Mas]

Hello PowerBI Experts,

I am new to PowerBI and trying to solve one issue.  I am sure the solution is obvious to all experts here:)

My data looks like this:

I need to create the Highest Status column using the following logic.

For each order number, the Highest Stage should be captured from Status when the stage in equal to highest stage.

Each order had candidates who went through numerous stages but all end at the last stage. Some orders have more stages than others so Highest Stage could be any number. 

I am only interested in the final (higest stage) for each order.

Any hints are appreciated.

Thank you,

Renee

[ImkeF · Accepted solution]

Hi @Renee_Mas ,
you can group your data on order number to retrieve the maximum value like so:

then expand the "Partition" Column to get back the fields that haven't been grouping columns.
That will return the higest stage for all fields.
Now, if you want to identify only those rows who were the higest stage, you can add additional column with formulas like this:
if [Stage] = [Hightest Stage] then [Hightest Stage] else null

You can also paste the following code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WMjYxUtJR8k9LSy1KTQGyTJRidTBFjcCihkbGZriFXVKTczLzYIbEAgA=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [#"Order Number" = _t, Status = _t, Stage = _t]
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {{"Order Number", Int64.Type}, {"Status", type text}, {"Stage", Int64.Type}}
    ),
    #"Grouped Rows" = Table.Group(
        #"Changed Type",
        {"Order Number"},
        {
            {"Hightest Stage", each List.Max([Stage]), type nullable number},
            {
                "Partition",
                each _,
                type table [Order Number = nullable number, Status = nullable text, Stage = nullable number]
            }
        }
    ),
    #"Expanded Partition" = Table.ExpandTableColumn(
        #"Grouped Rows",
        "Partition",
        {"Status", "Stage"},
        {"Status", "Stage"}
    ),
    #"Added Custom" = Table.AddColumn(
        #"Expanded Partition",
        "Is Higest Stage",
        each if [Stage] = [Hightest Stage] then [Hightest Stage] else null
    ),
    #"Added Custom1" = Table.AddColumn(
        #"Added Custom",
        "Highest Status",
        each if [Is Higest Stage] = null then null else [Status]
    )
in
    #"Added Custom1"

Next time, please provide sample data so the answerers don't have to create them themselves:
How to provide sample data in the Power BI Forum - Microsoft Power BI Community

[Renee_Mas]

Hello Imke,

My problem became a bit more complex and now for each order number, I need to identify the Highest Application Status (last column in the data sample) per order per candidate. 

I need to identify the highest stage highest stage each candiate has attained (which I did) and 

what that highest stage was (per order number, per candidate).

As before, each order had candidates who went through numerous stages but each ends up at the last stage. Some orders have more stages than others so Highest Stage could be any number. 

I am only interested in the final (higest stage) for each order.

Thank you,

ps.  I liked your previous solution and wonder if that one extra step might be obvious to you. 

I will also post it again.

Thank you for all the hits and pointers so far!

Renee

[Renee_Mas]

Order ID	Candidate name	Application Status	Stage		Highest Stage for candidate	Higest Application Status
1	Amba	New	1		6
1	Amba	Offer Accepted	6		6	Offer Accepted
1	Amba	Offer Cancelled	3		6
1	Amba	Offer Pending Approval	4		6
1	Amba	Offer presented	2		6
1	Amba	Offer Sent	5		6
1	Henriette	New	1		1	New
1	Jaspreet	Declined	3		3	Declined
1	Jaspreet	New	1		3
1	Jaspreet	Offered	2		3
1	Krista	New	1		4
1	Krista	Offer Pending Approval	4		4	Offer Pending Approval
1	Krista	Pre-Screen	2		4
1	Krista	Presented to HM	3		4
1	Sorus	New	1		1	New
6	Claudia	Declined	2		2	Declined
6	Claudia	New	1		2
6	Madison	New	1		2
6	Madison	Offered	2		2	Offered
6	Nadia	Declined	3		3	Declined
6	Nadia	New	1		3
6	Nadia	Rejected by TAP	2		3
9	Rupinder	New	1		1	New
9	Sukhman	New	1		2
9	Sukhman	Pre-Screen	2		2	Pre-screen
9	Victoria	Declined	2		3
9	Victoria	Declined	3		3	Declined
9	Victoria	New	1		3
12	ADEL	Declined	3		3	Declined
12	ADEL	In-Progress	2		3
12	ADEL	New	1		3
12	ALIREZA	Declined	3		3	Declined
12	ALIREZA	In-Progress	2		3
12	ALIREZA	New	1		3
12	ASSEM	Declined	3		3	Declined
12	ASSEM	In-Progress	2		3
12	ASSEM	New	1		3
12	DIANE	New	1		3
12	DIANE	Offer presented to candidate	2		3
12	DIANE	Offered	3		3	Offered

[Renee_Mas]

Hello Imke,

Thank you. The solution works. Thank you so much.

My problem became a bit more complex with another variable and I will try to outline by attaching a new file as per your instructions. 

Thanks again for your prompt response!

Renee