Status column based on another table

[WilliamAzevedo]

Hello, everyone.

I manage projects through a table and it's Statuses through a proper table with that goal. Example:

Statuses

Status	Project	Date
Canceled	P3	12/01/2024
Completed	P2	10/23/2024
Started	P1	12/15/2024
Started	P2	09/05/2024
Started	P3	11/18/2024

Projects:

Project	Status
P1	Started
P2	Completed
P3	Canceled

As above, I would like the Status column to show the Status info based on the most recent date of the Status column in the Statuses table. The tables are linked by the Project column. Is it possible?

Thank you in advance!

[WilliamAzevedo]

The formatting was weird and I had to delete my first reply. I hope it goes right this time.

The first time I tried, I had this error:

A single value for column '…' in table '…' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Then I searched in the forums what it meant and I found this topic: Solved: Re: A Single Value for Column Cannot Be Determine... - Microsoft Fabric Community

I followed the instructions Greg_Deckler posted here: Solved: Re: A Single Value for Column Cannot Be Determine... - Microsoft Fabric Community

My code ended up this way:

UltimoStatus = 
VAR proj_at = MAX(lst_projetos_unfa[Projeto e Processo])
VAR ult_data =
    MAXX(RELATEDTABLE(lst_evolucao_projetos_unfa), lst_evolucao_projetos_unfa[Data])
RETURN
    CALCULATE(
        MAX(lst_evolucao_projetos_unfa[Status]),
        lst_evolucao_projetos_unfa[Data] = ult_data,
        lst_evolucao_projetos_unfa[Projeto e Processo] = proj_at
    )

An it worked! I hope the post goes correctly this time and I can properly say thank you!

[WilliamAzevedo]

I'm having problems to reply. I will leave a complete reply here soon.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Status CC =
VAR _currentproject = Projects[Project]
VAR _latestdate =
    MAXX ( RELATEDTABLE ( Statuses ), Statuses[Date] )
RETURN
    CALCULATE (
        MAX ( Statuses[Status] ),
        Statuses[Date] = _latestdate,
        Statuses[Project] = _currentproject
    )