Filter Out single row data based on the multiple rows

Problem Statement: Below is my data

The table has two data of Project A, one generated on 11/10 and another on 11/09.

I want to create a master table which will give me few things like “Distinct Project Name, Last_Execution_DateTime, Overall Status”

  1. Distinct Project Name means it should give only Project A in first row, if Project B in second Row
  2. Last_Execution_DateTime Example Project A ran on 11/10, the most last execution datetimewas 11/10/2023 21:20:00
  3. Overall status means if Status of Project A executed on 11/10 has any one FAIL status, overall status should be FAIL otherwise PASS.

Expected Result should be:

Project Name Last Execution Date Overall Status
Project A 11-10-2023 21:20:00 FAIL

Hi @mohit.itsector,

To get the last execution date, you just have to create a calculated field using the max function:
max(Last_Execution_Date_Time)

Next, count the number of FAIL statuses per project.
FAIL count = countOver(ifelse(Status = ‘FAIL’, Status, null), [Project_Name], PRE_AGG)

Finally get the overall status:
Overall Status = ifelse({FAIL count} > 0, ‘FAIL’, ‘PASS’)

2 Likes

I have added few more projects with multiple dates, I am also getting right “failed” count, as well as overall status , however, I am getting OverAll_Status for all the dates which I am not suppose too. I am suppose to get only for Last_Execution_Date Only.

Please Note: I am using “table” and attach is the data I am using for dataset

Continue…
Please find my data below

Hi @mohit.itsector,

Can you use maxOver instead of max to calculate Last_Execution_Date_Time?
maxOver({Last_Execution_Date}, [Project_Name], PRE_AGG)

Create a field like this:
Is Last Execution Date = ifelse(Last_Execution_Date = Last_Execution_Date_Time, ‘Yes’, ‘No’)

Then filter your visual by:
Is Last Execution Date = Yes

You are awesome. Its solved my issue.

1 Like