Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
Really appreciate your help Sinnes. You are absolutely genius.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |