Rollup the date based on condition

[Anonymous]

Hello Everyone

I would like to achieve the rollup for each Category -  with the max date where color is not equal to red
Below is the sample data - 

I am getting the wrong highlighted dates - (it should filter out the date where it is red)

Result Expected For highlighted date- 
A - 11/18/2021
B - 10/11/2021

Thanks in advance. Would really appreciate the help.

[AlexisOlson · Accepted solution]

Try this:

Lastest Date =
IF (
    HASONEVALUE ( Table1[Color] ),
    MAX ( Table1[Date] ),
    CALCULATE ( MAX ( Table1[Date] ), Table1[Color] <> "Red" )
)

[Anonymous]

@AlexisOlson - 

Using the above measure you suggested is taking longer to process the data. 

Is there a way to use any other formula instead? 
I also learnt that the Hasonevalue takes longer time to process the query. 

[AlexisOlson]

See if this works any faster:

Lastest Date =
IF (
    HASONEVALUE ( Table1[Color] ),
    MAX ( Table1[Date] ),
    CALCULATE ( MAX ( Table1[Date] ), KEEPFILTERS ( Table1[Color] <> "Red" ) )
)

[Anonymous]

Thanks a lot. This is so helpful.