Bring back single value from a list based on a filter condition

[Anonymous]

Hello,

I have two tables:

Users

Name      Id

bob	1
Joe	2

Results

Id            UserId    CreatedOn    TestName  Passed

1	1	01/02/2017	"Test1"	False
2	1	01/04/2017	"Test1"	True
3	1	01/08/2017	"Test1"	True
4	1	01/02/2017	"Test2"	False
5	2	04/04/2017	"Test1"	False
6	2	04/03/2017	"Test1"	True
7	2	05/03/2017	"Test2"	False

I'm trying to write a DAX query to retrieve the result of the latest record for a user with a filter for a certain test name. In this example, I want to bring the "Passed" column back for Bob where test = "test1". I could filter the table using a CALCULATE and applying a TestName = "Test1" but I have no idea how I would then bring back the result for row 3 (the one with the highest date). 

Does anyone know the best way to approach this problem?

Many thanks,

Chris

[v-shex-msft]

Hi @Anonymous,

You can try to use below measure to get last date status based on user id and test:

Measure =
CALCULATE (
    LASTNONBLANK ( Table1[Passed], [Passed] ),
    VALUES ( Table1[UserID] ),
    VALUES ( Table1[Text] ),
    LASTDATE ( VALUES ( Table1[Date] ) )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous,

Try this measure and create a table visual with User and the measure to see if each user passed the test or not:

Measure =
CALCULATE (
    VALUES ( Table_Passed[Passed] );
    Table_Passed[Test Name] = "Test1";
    LASTDATE ( Table_Passed[CreateOn] )
)

Regards.

[Anonymous]

Hi FelipeLima,

Thanks for the response.

Unfortunately I'm getting the below:

"Something's wrong with one or more fields: (Users) A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.

I believe this is because the same date can appear multiple times for a user.