Check if latest values exists

[AparnaJ]

Hi,

Here is my data:

We need to show latest record, and for person "David", the latest record 1/22/2022 row will be displayed. Challenging part is need to show if Symptom 1 or 2 from this date existed in past. Fever is new symptom and should be highlighted here. Is there a way to do this in Power BI. Any help is appreciated. Thank you.

[v-xiaosun-msft · Accepted solution]

Hi @AparnaJ ,

According to your description, I made the sample and here is my solution.

Create a column to return the latest date of each person.

latestdate = 
MAXX (
    FILTER (
        'Table',
        'Table'[Symptom1] = EARLIER ( 'Table'[Symptom1] )
            || 'Table'[Symptom2] = EARLIER ( 'Table'[Symptom2] )
    ),
    'Table'[Date]
)

Then create a column to show if Symptom 1 or 2 from this date existed in past.

Column = 
IF (
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Person] = EARLIER ( 'Table'[Person] )
                && 'Table'[Date] = [latestdate]
        )
    ) > 0,
    TRUE ()
)

Then create a new table to only show the row of the latest record.

Table 2 = 
CALCULATETABLE ( 'Table', 'Table'[Date] = 'Table'[latestdate] )

In new table, create a measure to use "conditional format".

highlight = 
IF (
    MAX ( 'Table 2'[Symptom1] ) = "Fever"
        || MAX ( 'Table 2'[Symptom2] ) = "Fever",
    1
)

Then right click "Symptom1".

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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