Find the higher status per fiscal year

[crolland]

Good morning,

I have the feeling to be in a large black hole... I tried some much things...I am now confused...

Here is my initial table : 

Fiscal Year	Project	Project Status	Roadmap
FY 2022/2023	Project A	On Going	OMEGA
FY 2022/2023	Project B	On Going	OMEGA
FY 2022/2023	Project C	On Going	OMEGA
FY 2022/2023	Project D	At Risk	CLIO
FY 2022/2023	Project E	On Going	BETA
FY 2022/2023	Project F	Blocked	BETA
FY 2023/2024	Project A	At Risk	OMEGA
FY 2023/2024	Project C	On Going	OMEGA
FY 2023/2024	Project D	Not Started Yet	CLIO
FY 2023/2024	Project G	On Going	CLIO

And my goal is to get the status of the Roadmap per fiscal year knowing the following logic : 

1. Blocked (Higher status)

2. At Risk

3. On Going

4. Not Started Yet (Lower status)

If one of the project in the roadmap item is Blocked, the roadmap status is Blocked.

If one of the project in the roadmap item is At Risk and none of them is Blocked, the roadmap status is At Risk

etc...

like : 

Fiscal Year	Roadmap	Roadmap Status
FY 2022/2023	OMEGA	On Going
FY 2022/2023	CLIO	At Risk
FY 2022/2023	BETA	Blocked
FY 2023/2024	OMEGA	At Risk
FY 2023/2024	CLIO	On Going

Thanks !

[v-luwang-msft · Accepted solution]

Hi @crolland ，

Pls use the below dax to create two column:

flag = IF('Table'[Project Status]="Blocked",4,IF('Table'[Project Status]="At Risk",3,IF('Table'[Project Status]="On Going",2,1)))

test = CALCULATE(MAX('Table'[flag]),ALLEXCEPT('Table','Table'[Fiscal Year],'Table'[Roadmap]))

Then create a tableB:

Create relationship:

Output result:

Best Regards

Lucien