Forum Discussion
Creating a Table with Distinct values based on Expression & add Additional Columns
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?
4 Replies
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]
)
