Forum Discussion
Creating a Table with Distinct values based on Expression & add Additional Columns
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.
Thanks - but this has not resulted in a unique column A
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]
)This is closer to distinct, but unfortunetaly still not unique
