topic Re: How to aggregate rows based on columns in DAX Commands and Tips

How to aggregate rows based on columns

Praj — Thu, 15 Dec 2022 13:58:01 GMT

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

Re: How to aggregate rows based on columns

FreemanZ — Thu, 15 Dec 2022 14:31:25 GMT

Hi Praj

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

Re: How to aggregate rows based on columns

Praj — Thu, 15 Dec 2022 14:39:00 GMT

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)

Re: How to aggregate rows based on columns

Anonymous — Wed, 28 Dec 2022 07:05:14 GMT

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.