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.
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 |
Solved! Go to 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.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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?
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.
@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)
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.
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |