Checking for max value in summarized table DAX help

[KasperBI]

Hi everyone. I have data that looks like this:

TestCaseID	Configuration	RunDate
1	1	08-04-2021 11:10:10
1	1	08-04-2021 12:10:10
1	2	08-04-2021 13:10:10
2	1	08-04-2021 08:08:08
2	2	08-04-2021 09:09:09
2	2	08-04-2021 10:10:10
3	1	08-04-2021 09:09:09
3	1	08-04-2021 11:11:11
3	2	08-04-2021 10:10:10

I have a table in my PBI report where the user can select test cases, and whatever configurations they want through slicers. I want the table to only display the latest test run within the configuration. 

So I ideally would create a calculated column that checks whether the current test/configuration is the last one for the configuration. The data output should look something like this:

TestCaseID	Configuration	RunDate	FilterCheck
1	1	08-04-2021 11:10:10	Remove
1	1	08-04-2021 12:10:10	Keep
1	2	08-04-2021 13:10:10	Keep
2	1	08-04-2021 08:08:08	Keep
2	2	08-04-2021 09:09:09	Remove
2	2	08-04-2021 10:10:10	Keep
3	1	08-04-2021 09:09:09	Remove
3	1	08-04-2021 11:11:11	Keep
3	2	08-04-2021 10:10:10	Keep

This is the code I have so far:

LatestTest =

VAR MaxDateForCombo =

//returns a table with a combined column from test ID and Config name and the maximum (latest) test result.

SUMMARIZE (

ADDCOLUMNS (

'Test Results',

"TestIDConfig", CONCATENATE ( 'Test Results'[TestcaseId], 'Test Results'[Configuration] ),

"RunDate2", MAX ( 'Test Results'[RunDate] )

),

[TestIDConfig],

"MaxRunDate", MAX ( 'Test Results'[RunDate] )

)

//the current row context date

Var Currentdate = 'Test Results'[RunDate]

// If the current date is smaller than the maximum date for the selected combinations of runID and config then filter it out.

Var FilterCheck = IF(Currentdate<

,"Filter out","Keep in")

RETURN

FilterCheck


I am having problems with the filtercheck part. I thought about using lookupvalue on the summarized table to see if rundate2 was bigger than rundate, but I can't figure it out. can someone help me out to get to my desired result? 🙂

[Jihwan_Kim · Accepted solution]

Hi, @KasperBI 

Please correct me if I wrongly understood your question.

Please check the below Calculated Column and the link down below, which is the sample pbix file.

Latest Test Column =
VAR lastnonblankcheck =
CALCULATE (
LASTNONBLANK ( 'Test Results'[RunDate], MAX ( 'Test Results'[Configuration] ) ),
ALLEXCEPT (
'Test Results',
'Test Results'[TestCaseID],
'Test Results'[Configuration]
)
)
RETURN
IF ( 'Test Results'[RunDate] = lastnonblankcheck, "Keep", "Remove" )

https://www.dropbox.com/s/xxpqyuj3xk6l2or/KasperBI.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[KasperBI]

Thank you so much, that does seem to work.