Forum Discussion

JustinDoh1's avatar
JustinDoh1
Post Prodigy
5 years ago
Solved

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

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.