Creating a Table with Distinct values based on Expression & add Additional Columns

[AverageBiUser]

How do I create a copy of a table that has distinct values for column A, with the distinct values for this column are being filtered for hte max columns in column B, whilst also adding additonal columns to the table.

At the moment, I have this expression:

SUMMARIZE( Table, Table[Colum A], "Max Value", MAX(Table [Column B])). This returns a distinct table, filtered correcty for my expression.

However, when I add columns C & D to the summary table function, I no longer get a distinct table based on column A, (likely because individual values for column A can have mutliple values for column C and/or D).

I want to add these columns to the table, & return only the value for column's C & D that are also filtered for the column B expression. How do I do this?

[rubayatyasmin]

Hi, @AverageBiUser 

try this code

NewTable =
SUMMARIZE(
ADDCOLUMNS(
SUMMARIZE(
Table,
Table[Column A],
"Max Value", MAX(Table[Column B])
),
"Max Row",
CALCULATE(
MAX(Table[Column B]),
FILTER(
Table,
Table[Column A] = EARLIER(Table[Column A])
&& Table[Column B] = EARLIER(Table[Column B])
)
)
),
[Column A],
[Max Value],
"Column C",
CALCULATE(MAX(Table[Column C]), ALLEXCEPT(Table, Table[Column A])),
"Column D",
CALCULATE(MAX(Table[Column D]), ALLEXCEPT(Table, Table[Column A]))
)

you need to adjust the code with your actual column and table name.

[AverageBiUser]

Thanks - but this has not resulted in a unique column A

[rubayatyasmin]

use Rankx 

NewTable =
VAR TempTable =
ADDCOLUMNS(
Table,
"Rank",
RANKX(
FILTER(Table, [Column A] = EARLIER([Column A])),
[Column B],
,
DESC,
Dense
)
)
RETURN
SELECTCOLUMNS(
FILTER(TempTable, [Rank] = 1),
"Column A", [Column A],
"Max Value", [Column B],
"Column C", [Column C],
"Column D", [Column D]
)

[AverageBiUser]

This is closer to distinct, but unfortunetaly still not unique