Display the value in a column that matches the maximum date

[Syndicate_Admin]

Good

I need to show in the Last Incident Status column, the value that matches the Last Hour field

In the table I have marked in yellow the problem I have, the calculation of the last hour is correct, but when I search with MAX, in the other column, being text, it should not do well

I have put a table with the problem I have, the incidence INFORM/00056 has two records one of 09/19/2023 as canceled and another of 09/14/2023, I want to show in the last incidence status column, the state that coincides with the maximum date, but it does not work for me, for this example I should put canceled that is the maximum date (09/19/2023), I do not know why it shows closed, that even that incidence does not have that state

The Last Minute column has this formula

last minute = CALCULATE (.MAX ( 'InformeSAR'[Date of implementation] ),FILTER ( 'InformeSAR', [Incidents] = EARLIER ( 'InformeSAR'[Incidents] )))

the last state incidence

Last incidence status = CALCULATE (

.max( 'InformeSAR'[Incident Status]),

FILTER ( 'InformeSAR', [Date of implementation] = earlier( InformeSAR[last minute] ) )

)

Thanks a lot.

Best regards

[Syndicate_Admin]

Can anyone help me please?

[Syndicate_Admin]

no works

[Syndicate_Admin]

It doesn't work, I've changed the formula and it now shows the following:

All put them as DETECTED, I think max with text fields does not work, really the calculation would be to put the value that matches, not the max

[123abc]

I see that you've updated the formula, but it's still not producing the desired results. If you want to display the incident status that matches the maximum date for each incident, you should indeed use a different approach. You can achieve this using a combination of SUMMARIZE, ADDCOLUMNS, and EARLIER functions. Here's the revised formula:

Last incidence status =
VAR MaxDate = MAXX(
FILTER('InformeSAR', 'InformeSAR'[Incidents] = EARLIER('InformeSAR'[Incidents])),
'InformeSAR'[Date of implementation]
)
RETURN
MAXX(
FILTER('InformeSAR', 'InformeSAR'[Date of implementation] = MaxDate),
'InformeSAR'[Incident Status]
)

This formula first calculates the maximum date (MaxDate) for the current incident by filtering the table based on the current incident using EARLIER. Then, it finds the incident status that corresponds to the maximum date for that specific incident.

This should give you the correct incident status for each incident based on the maximum date.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[123abc]

I see the issue you're facing, and you're correct that using MAX with text fields may not give you the desired result. In your case, you want to display the incident status that matches the maximum date, not the maximum incident status.

You can achieve this by using the following DAX formula for the "Last incidence status" column:

Last incidence status =
VAR MaxDate = MAX('InformeSAR'[Date of implementation])
RETURN
CALCULATE(
MAX('InformeSAR'[Incident Status]),
FILTER('InformeSAR', 'InformeSAR'[Date of implementation] = MaxDate)
)

This formula calculates the maximum date in the "Date of implementation" column and stores it in the variable MaxDate. Then, it uses the CALCULATE function with the FILTER function to find the maximum incident status for the rows where the "Date of implementation" matches the MaxDate. This should give you the incident status that corresponds to the maximum date.

Please make sure to replace the existing formula in your "Last incidence status" column with the above formula, and it should display the correct incident status that matches the maximum date.

[123abc]

It appears that you're working with Power BI and trying to display the value in a column that matches the maximum date from another column. In your case, you want to show the Last Incident Status column, which should display the status that corresponds to the maximum date in the Date of Implementation column for each incident. However, your current DAX formulas are not producing the desired results.

To achieve your goal, you can use the following DAX formula for the Last Incident Status column:

Last Incident Status =
VAR MaxDate = MAX('InformeSAR'[Date of Implementation])
RETURN
CALCULATE(
MAX('InformeSAR'[Incident Status]),
FILTER('InformeSAR', 'InformeSAR'[Date of Implementation] = MaxDate)
)

Here's how this formula works:

1. We define a variable MaxDate that calculates the maximum date from the Date of Implementation column for each row.

2. We then use the CALCULATE function to find the maximum Incident Status for the rows where the Date of Implementation matches the MaxDate calculated in step 1.

This formula should give you the correct Last Incident Status for each incident based on the maximum date. Replace your existing Last Incident Status formula with this one in Power BI, and it should work as expected.