How to aggregate rows based on columns

[Praj]

Hello Everyone, 

I have the below source table where each account has the RAG status. 

I am trying to create a new output table from the above pasted table to get the unique account name and respective status. Below is the output table I am looking for using DAX. 

I tried the lookup however, due to accounts associated with multiple status it throwed an error. Any approach on this problem will help me a lot.

TIA

Cheers,

Praj

[Anonymous · Accepted solution]

Hi @Praj ,

Please try:

Table 2 = DISTINCT( SELECTCOLUMNS('Table',"Account",[Account],"Status",[Status]))

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

[FreemanZ]

Hi @Praj 

In case of multiple status for one account, which do you expect to return?

[Praj]

Hi @FreemanZ , 

Thanks for your response. 

In case of multiple status, I am expecting the worst case. (If single account has red and green status, I am looking for red as the final status in my output table)