Find single value using AND condition

[former_member600589]

I'm trying to pull the max value based on multiple conditions. I have a report with the following fields: ID, Entry date, type of test, score. There are 4 different types of tests with numerous entry dates.

For each test, I want to be able to pull the most recent score based on the entry date. I just can't seem to get the two conditions to work together.

I have a max function in ID pulling the correct max date. But when I try to get the score using the max date equaling the entry date, I can't get the and part of the formula to work. I need it to also be based on the score so that I have 4 different values coming out of the single column containing all scores.

[amitrathi239]

Can you please share the sample data set.

[former_member600589]

I figured it out, it ended up being a parenthesis issues with my where statement and it worked.

Tom - I originally tried that method and was pulling the max value that way but it wouldn't always work. 90% of the time it would give me a value back but random lines returned null values. Luckly my where function was able to work and solved my issue.

[Tom_N8]

Hi Logan,

Have you considered using a different approach, i.e. would filtering on the table block not be sufficient for you? I have created a couple of variables:

[Max Date in Test] =Max([EntryDate]) In([TypeOfTest])

[Filter] =If([EntryDate] = [Max Date in Test]) Then "Filter On Me"

These return the desired result, i.e. in my table (consisting of two or three columns only, I get one row per Type of Test with the Date and its corresponding score correctly returned. A table filter sits on [Filter]:

Regards,

Tom