How to get the Value based on MAX(date) - using variable

[JustinDoh1]

This is set of data, and I am trying to express my filters in a measure.

I have a data that have two Steps (1 & 2) and different dates (ConsentDate).

I am trying to determine the final outcome (either 1 or 0 depending on Step and ConsentDate).

1st: Look for latest date (considering NULL data) for each Step (1 or 2):

In this case, for Step =1, the output of Consent for latest ConsentDate is "Consented", it will select "Consented."

For Step=2, the  the output of Consent for latest ConsentDate (as there is no other date to compare) is "Refused", so it will select "Refused".

2nd: Output:

If output for Step 1 is "Consented" or "Historical", output for [StepOne] = 1. 

If output for Step 1 is "Refused", output for [StepOne] = 0. 

If output for Step 2 is "Consented" or "Historical", output for [StepTwo] = 1.

If output for Step 2 is "Refused", output for [StepTwo] = 0.

I am stuck on this area 1st logic where I need to express:

Find the [Consent] in Step1 where [ConsentDate] is MAX.

Like in SQL: Select [Consent] from Step1 having MAX[ConsentDate] 

I am sharing my Pbix file here.

The measure is called "Step1_DEV".   

Appreciated for help.

[Greg_Deckler · Accepted solution]

@JustinDoh1 See if Lookup Min/Max helps: Lookup Min/Max - Microsoft Power BI Community

[JustinDoh1]

@Greg_Deckler Thank you so much. I will dig in more. 🙂