Compare and replace values in a table with the higher value

[Anonymous]

I have table "Days" like below:

Day	Severity
Monday	Low
Friday	High
Wednesday	Low
Wednesday	High
Saturday	Medium
Wednesday	Medium
Monday	Critical

How can I compare values in "Severity" based on the value in "Day", and replace with the highest value? Example output below.

Day	Severity
Monday	Critical
Friday	High
Wednesday	High
Wednesday	High
Saturday	Medium
Wednesday	High
Monday	Critical

I also have a table "Severities" like below, which shows the ranking of severities:

Index	Severity
1	Critical
2	High
3	Medium
4	Low

[Jihwan_Kim]

Severity Replace CC =
VAR currentday = Days[Day]
VAR _comparisontable =
ADDCOLUMNS (
SUMMARIZE ( FILTER ( Days, Days[Day] = currentday ), Days[Day], Days[Severity] ),
"@severityindex", RELATED ( Severities[Index] )
)
VAR _severityselect =
MINX (
FILTER (
_comparisontable,
[@severityindex] = MINX ( _comparisontable, [@severityindex] )
),
Days[Severity]
)
RETURN
_severityselect

https://www.dropbox.com/s/d6yt2hod83g2jx5/sabre.pbix?dl=0 

[Anonymous]

Thanks Jihwan. This solution works, but I was hoping to do it in Power Query rather than DAX. Do you know if it can be done in PQ?

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Try the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PS0msVNJR8skvV4rViVZyK8qECHhkpmeARcJTU/JSi1FVIYvBFQYnlpQWQcR8U1MyS3MxlCIJwy12LsosyUxOzFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, Severity = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Day", type text}, {"Severity", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Severity"}, Severities, {"Severity"}, "Severities", JoinKind.LeftOuter),
    #"Expanded Severities" = Table.ExpandTableColumn(#"Merged Queries", "Severities", {"Index"}, {"Severities.Index"}),
    #"Grouped Rows" = Table.Group(#"Expanded Severities", {"Day"}, {{"AllRows", each _, Value.Type(#"Expanded Severities")}, {"Minimum Index", each List.Min([Severities.Index]), type number}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Index"}, {"AllRows.Index"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded AllRows", {"Minimum Index"}, Severities, {"Index"}, "Severities", JoinKind.LeftOuter),
    #"Expanded Severities1" = Table.ExpandTableColumn(#"Merged Queries1", "Severities", {"Severity"}, {"Severities.Severity"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Severities1",{{"AllRows.Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"AllRows.Index", "Minimum Index"})
in
    #"Removed Columns"

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , I am not clear on the logic of how you got table two. Do you have multiple date data in table 1 and you updated that with same status of weekday (highest)

[Anonymous]

If you filter table 1 for just "Wednesday", you get the table below:

Day	Severity
Wednesday	Low
Wednesday	High
Wednesday	Medium

The highest severity in this group is "High", so we replace the other values with this value.

Same applies if you filter for "Monday". There are two severities on that day, "Low" and "Critical". Critical is higher, so we replace the "Low" value with "Critical". I hope this clears it up.