Forum Discussion
Status column based on another table
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!
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 )
3 Replies
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 )I'm having problems to reply. I will leave a complete reply here soon.
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!
