List highest score for each provider from a range of tables.

[acg]

I have a table with the provider and up to  5 different countries they deal with. THe risk scores have been displayed. I need to be able to say what is the highest risk score for each provider and year.

If there are counties with the same risk score, then the country that appears first is taken. 

IN SQL I solved this sort of thing like this:

, [Highest_Risk] AS(
SELECT *,
(SELECT MAX(v)
FROM (
VALUES (Country_change.[High_risk_country1]),
(Country_change.[High_risk_country2]),
(Country_change.[High_risk_country3]),
(Country_change.[High_risk_country4]),
(Country_change.[High_risk_country5])) AS value(v)

What do you do in Power BI?

Provider	Country 1 risk score	Country 2 risk score	Country 3 risk score	Country 4 risk score	Country 5 risk score	Highest risk score
1	0	0	3	2	1	3
2	3	3	4	5	2	5
3	2	5	5	3	2	3
4	0	0	0	1	0	1
5	0	0	0	0	0	0

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTKAYmMgNgJiQ6VYnWgwyxiKTYDYFCwLkoGpM4ViY7iMCZJpBmCTQDRIxhRNBoJjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Provider = _t, #"Country 1 risk score" = _t, #"Country 2 risk score" = _t, #"Country 3 risk score" = _t, #"Country 4 risk score" = _t, #"Country 5 risk score" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Provider", Int64.Type}, {"Country 1 risk score", Int64.Type}, {"Country 2 risk score", Int64.Type}, {"Country 3 risk score", Int64.Type}, {"Country 4 risk score", Int64.Type}, {"Country 5 risk score", Int64.Type}}),

    Max = Table.AddColumn(#"Changed Type", "Max", each List.Max(List.Skip(Record.ToList(_))))
in
    Max

[smpa01]

@acg  you can do this

highest = 
MAXX (
    {
        MAX ( tbl[Country 1 risk score] ),
        MAX ( tbl[Country 2 risk score] ),
        MAX ( tbl[Country 3 risk score] ),
        MAX ( tbl[Country 4 risk score] ),
        MAX ( tbl[Country 5 risk score] )
    },
    [Value]
)

[FreemanZ]

hi @smpa01 @acg 

that is quite an eye-opener, perfect if there are not too many country columns.

follow the logic, it is also good to add a calculated column like:

ColumnHighest = 
MAXX (
    {
        [Country 1 risk score],
        [Country 2 risk score],
        [Country 3 risk score],
        [Country 4 risk score],
        [Country 5 risk score]
    },
    [Value]
)

[FreemanZ · Accepted solution]

hi @acg 

1) try to unpivot it in Power Query Editor. Write click the Provider Column and choose unpivot other columns. You make it like this:

2) then plot a table visual with attribute column and a measure as simple as:

Measure = MAX(TableName[Value])

it worked like: