Fetching the last status from a related table with 1:N relationship

[db121]

Hi,
I have a ticketing system build on Sharepoint and reporting via PowerBI. I have two tables viz. "Ticket" and "Ticket_Status". The table "Ticket_Status" stores the day to day progress against each ticket. I need to show the last status against each ticket in the "Ticket" table which is fetched from "Ticket_Status". Refer the sample data below:-

Relationship
Ticket[ID] 1 * Ticket_Status[RefID]

Ticket[ID] (One) --> Ticket_Status[RefID] (many)

I would like to create a new column in table "Ticket" named "CurrentStatus" which should fetch the values from the field "Status" in the table "Ticket_Status" based on the maximum available "Date" value.

The RELATED function doesn`t seem to work as the relationship is the otherway round hence I have tried the LOOKUPVALUE function but with error. My formula for 'Ticket'[CurrentStatus] is as follows:- LOOKUPVALUE('Ticket_Status'[Status],'Ticket_Status'[RefID],'Ticket'[Id],'Ticket_Status'[Date],MAXA('Ticket_Status'[Date]))

The error is:- Function 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

Whats wrong with my formula? Is there a better way to get it done apart from the LOOKUPVALUE function?

Thanks for any help.

Regards,

Deb

[db121]

Thank you @DataInsights  the solution worked like a charm.

[DataInsights · Accepted solution]

@db121,

Try this calculated column in table Ticket:

CurrentStatus = 
VAR vMaxDate =
    CALCULATE ( MAX ( Ticket_Status[Date] ), RELATEDTABLE ( Ticket_Status ) )
VAR vResult =
    CALCULATE ( MAX ( Ticket_Status[Status] ), Ticket_Status[Date] = vMaxDate )
RETURN
    vResult