Filter a table to return a table with distinct id.

[Jeff_Ng]

How can I transform below the "Input Table" to look like the "Output Table" using DAX in Powe BI?

First I need to retrieve maximum number from each 'risk_score_X' then sum them up to produce 'Max_total_score' then return a single row for each 'project_id'. 

Your helps will be very much appreciated. Thanks.

Input Table
project_id	risk_score_1	risk_score_2	risk_score_3	risk_score_4	total_score
20AB123	1	0	0	1	2
20AB123	0	2	1	0	3
20AB123	1	1	3	0	5
20AB123	0	1	0	4	5
30AB456	1	0	0	1	2
30AB456	0	2	1	0	3
30AB456	1	1	5	0	7
30AB456	0	1	0	5	6
Output Table
project_id	Max_of_risk_score_1	Max_of_risk_score_2	Max_of_risk_score_3	Max_of_risk_score_4	Max_of_total_score
20AB123	1	2	3	4	10
30AB456	1	2	5	5	13

[shinnes · Accepted solution]

Hello,

I am no expert, but how about something like this:

Summarized =
ADDCOLUMNS (
SUMMARIZE (
Table1,
Table1[project_id],
"Max_of_risk_score_1", CALCULATE ( MAX ( Table1[risk_score_1] ) ),
"Max_of_risk_score_2", CALCULATE ( MAX ( Table1[risk_score_2] ) ),
"Max_of_risk_score_3", CALCULATE ( MAX ( Table1[risk_score_3] ) ),
"Max_of_risk_score_4", CALCULATE ( MAX ( Table1[risk_score_4] ) )
),
"total_score", [Max_of_risk_score_1] + [Max_of_risk_score_2]
+ [Max_of_risk_score_3]
+ [Max_of_risk_score_4]
)

Perhaps someone else can recommend a simpler way to do.

[Jeff_Ng]

Really appreciate your help Sinnes. You are absolutely genius.