Forum Discussion
Create New Table With Condition
Hello Power BI heroes, I have a table which contains a log for cases, every case has a unique ID and belongs to a city. Every case has one more or statuses. I want to create a summarized table wh...
Hi Anonymous ,
Please have a try.
Manage the two tables relationship.
Create a measure.
Measure 2 = IF(SELECTEDVALUE('ID'[Status]) in VALUES('Table'[Status]),"Ture","False")Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Type: X23: statuses: T32 and T09
When you say "and" do you mean "or" ? Meaning the ID is True when any one condition is true?
First, create a reference table "Valids" that lists the "good" conditions
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijAyVtJRCjE2UorVgfMMLCE8QwMQz9AImWdsAOWBOCZGSByQTCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Status = _t])
in
Source
Then apply these transforms:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYowMgaSIcZGSrE6KCIGlggRoCxQxNAQLAJiRxiCBYzQBIwNwALGCFOgepBEkNVYGoBtgqgxAYmYgtUAxWNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Status = _t]),
#"Merged Queries" = Table.NestedJoin(Source, {"Type", "Status"}, Valids, {"Type", "Status"}, "Valids", JoinKind.LeftOuter),
#"Replaced Value" = Table.ReplaceValue(#"Merged Queries",each [Valids],each Table.RowCount([Valids]),Replacer.ReplaceValue,{"Valids"}),
#"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"Resolved", each List.Max([Valids]), type number}})
in
#"Grouped Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Thank you for your response and help 🙂 I actually meant "or". I will try your solution too. Thanks again.