Search code examples
excelpowerquerypowerpivotm

How to find all instances of single LocID, assess them to see if any Summary match "Bouncing", then return T/F if any instance does?

So, I'm trying to build a document that tracks which locations have tickets open with a summary of "Bouncing" in order to determine which sites need to have bouncing tickets opened for them. I have a report (Location List) with all locations listed (Columns: Location ID). I have another report (Ticket List) with all open tickets (Columns: Ticket Number, Location, Summary, Ticket Status). For context's sake, I have another report with alarms that occurred for each location (location ID, alarm type, date occurred, etc), but I don't think that's necessary for this.

Location List: example of location list report

  • Each Location only appears once

Ticket List: example of ticket list report

  • Ticket numbers are unique
  • A Location can have more than one ticket open at a time
  • I've created a relationship between the location IDs on the Ticket List and the Location List with Location list as Primary. (similar relationship exists with Location List and the alarm report)
  • There will only be OPEN tickets on the Ticket List (so status column is superfluous)
  • I also have merged queries so the Ticket List info (Ticket Number and Summary) appears on the Location List in the cases that there are tickets open.

Ultimately, I'm trying to have a pivot table based on the alarm info that will show whether or not a Location is a candidate for a Bouncing ticket (X alarms in X days), but will also ONLY show locations that do NOT have open Bouncing tickets. I have the first part down, but the only showing locations that don't have open bouncing tickets is where I've run into problems.

Initially, I thought I could just add a column to one of the queries like so:

 #"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "Bouncing Investigation Open?", each if [#"Ticket List.Summary"] = "Bouncing" then "Yes" else "No")

However, I've realized that, since a location can have more than one ticket open, a location can appear as both Yes and No when doing a pivot table to determine what does/doesn't already have a Bouncing ticket open.

Is there a way for me to tell it to look for each instance of a Location ID from the Location List and check to see if the Summary for ANY = "Bouncing" and if TRUE mark somewhere (doesn't matter where, new column in existing query or wherever) "Yes" and, if FALSE, "No"?

Solution

  • An (almost) purely UI driven way to do it would be

    • filter your rows so that you've only got the 'Bouncing' tickets enter image description here
    • then Group by Location enter image description here
    • then Merge your pre filter step with your Grouped step. For this you need to first Merge the table with itself, then change the formula to refer to the correct step (ie change the 2nd #"Change Type" table to the "Grouped Rows" table) enter image description here enter image description here enter image description here
    • Expand the Merge column with a rowcount enter image description here
    • Change Type to logical (1 = true 0 = false) enter image description here

    Then you can change your column names etc.