Forum Discussion
Solved
How to create a measure to filter data (desired column example below)
Hello everyone,
I have two tables below.
I explained what I need in desired column.
Is there anybody to help me a creating kind of measure?
Thanks in advance,
| ID | Task Statu | Task Name | Order | Description | Desired Output / Measure | |
| 15 | Completed | A | 1 | Retrieve | ||
| 15 | Waiting | B | 2 | Assestment | ||
| 15 | Not started | C | 3 | Review 1 | ||
| 15 | In progress | D | 4 | Initial snet | ||
| 15 | Not started | E | 5 | Complete 1 | ||
| 15 | Cancelled | F | 6 | Review 2 | ||
| 15 | Waiting | G | 7 | AB Review | Initial snet | If any task status is "In progress" belong a specific ID that Task Name should be writen to here in the row of AB Review, if there is no "In progress" status in that specific ID, here should be empty. |
| 27 | Not started | A | 1 | Retrieve | ||
| 27 | In progress | B | 2 | Assestment | ||
| 27 | Cancelled | C | 3 | Review 1 | ||
| 27 | Completed | D | 4 | Initial snet | ||
| 27 | Waiting | E | 5 | Complete 1 | ||
| 27 | Not started | F | 6 | Review 2 | ||
| 27 | Completed | G | 7 | AB Review | Assestment | If any task status is "In progress" belong a specific ID that Task Name should be writen to here in the row of AB Review, if there is no "In progress" status in that specific ID, here should be empty. |
| 27 | Completed | H | 8 | Final Sent | ||
| 28 | Not started | A | 1 | Retrieve | ||
| 28 | Waiting | B | 2 | Assestment | ||
| 28 | Waiting | C | 3 | Review 1 | ||
| 28 | Waiting | D | 4 | Initial snet | ||
| 28 | Waiting | E | 5 | Complete 1 | ||
| 28 | Waiting | F | 6 | AB Review | If any task status is "In progress" belong a specific ID that Task Name should be writen to here in the row of AB Review, if there is no "In progress" status in that specific ID, here should be empty. |
Why would this need to be a measure? Can the result be impacted by user interaction?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZI9D4IwEIb/SsPsAn7AKvi5OOjgQBiIXkiTUght8O97FA0t0EaHNznI0+vdA2nq+Wtv4SVVWTOQ8MR6i/ExV5ANhRawJF62+JD3nErKC6xiTNDxQoCQJXBpkpdKEiHzpu+aYJaqa0vhRXyTPXNSN1XRgBD4tMOs1Fu8K2dEcHD23mP0Lcbdk5w/gDHFHjCbYY7Att0RE3bbxaRHx/N0Z4JwMovVnmLNPR0GFa3PbfXXk9r3c9pT9LClw9zMblZ3kxnm7Glbzp45YaLuFspx6pthI/rDc/TrXzoi7YZNzu3XZF1+TfLrVjeGYPYG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Task Statu" = _t, #"Task Name" = _t, Order = _t, Description = _t, #"Desired Output / Measure" = _t]), #"Added Custom" = Table.AddColumn(Source, "Output",(k) => if k[Description] = "AB Review" then try (Table.SelectRows(Source,each [Task Statu]="In progress" and [ID]=k[ID])){0}[Description] otherwise null else null ) in #"Added Custom"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".
1 Reply
- lbendlin
Super User
Why would this need to be a measure? Can the result be impacted by user interaction?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZI9D4IwEIb/SsPsAn7AKvi5OOjgQBiIXkiTUght8O97FA0t0EaHNznI0+vdA2nq+Wtv4SVVWTOQ8MR6i/ExV5ANhRawJF62+JD3nErKC6xiTNDxQoCQJXBpkpdKEiHzpu+aYJaqa0vhRXyTPXNSN1XRgBD4tMOs1Fu8K2dEcHD23mP0Lcbdk5w/gDHFHjCbYY7Att0RE3bbxaRHx/N0Z4JwMovVnmLNPR0GFa3PbfXXk9r3c9pT9LClw9zMblZ3kxnm7Glbzp45YaLuFspx6pthI/rDc/TrXzoi7YZNzu3XZF1+TfLrVjeGYPYG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Task Statu" = _t, #"Task Name" = _t, Order = _t, Description = _t, #"Desired Output / Measure" = _t]), #"Added Custom" = Table.AddColumn(Source, "Output",(k) => if k[Description] = "AB Review" then try (Table.SelectRows(Source,each [Task Statu]="In progress" and [ID]=k[ID])){0}[Description] otherwise null else null ) in #"Added Custom"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".