Show the Tickets Assigned to Me (or Others)

I stumbled across an interesting problem recently when helping someone on a forum. Here is the scenario.

Assume there is a help desk that is logging tickets into a system. Each ticket is assigned to single staff member who is responsible to action that ticket. The requirement is that when the user (staff member) logs into the report, they want to be able to easily see the tickets that are assigned to them directly, but still needs to be able to see all other tickets if required. If this was a simple “only show me my tickets”, then this could be easily managed using role level security (RLS). But given the requirement is for the user to see all tickets if needed as well, then RLS won’t work in this case. What is really needed therefore is some sort of toggle to allow the user to show all tickets, or just their own tickets.

Sample Data

To simulate the scenario and the solution, I have created two tables as follows:

User table

This is lookup table for staff members, with a unique ID column being the online username (email address)

Tickets table

This is a data table containing all the tickets and the name of the staff member (user) the ticket is assigned to.

These two tables are connected as shown below

Now let me show you a few tricks on how I built the following working solution.

Trick 1: Leverage the USERNAME() Function

The way I decided to solve this problem was to write a measure that extracts the name of the user that is currently accessing the report, as follows:

myUserName = USERNAME()

This result displayed by the above measure is different depending if you are using Power BI Desktop or PowerBI.com. If you are using Power BI Desktop, it will display

ComputerName\User

If you are using PowerBI.com it will display your email address used to log in. You can see these 2 variations for my personal login in the user table shown above.

Next, I wrote a measure to display the count of my tickets as follows.

myTickets =
   VAR me = USERNAME()
   RETURN
      CALCULATE(COUNTROWS(Tickets), KEEPFILTERS(Users[Desktop] = me))

This measure shows the count of my tickets in Power BI Desktop report after having applied a filter for my username.

If I was going to deploy this online, I would write the formula differently as follows.

myTickets =
   VAR me = USERNAME()
   RETURN
      CALCULATE(COUNTROWS(Tickets), KEEPFILTERS(Users[Online] = me))

Trick 2: Create a Tickets Table (2 Versions)

I then created a tickets visual in the report (a table in this case) showing all the tickets as shown below.

I then duplicated the table above. In the duplicate copy of the table, I applied a “Filter on this visual” as is shown below. I used the measure I showed above as a filter with the settings myTickets > 0.

This has the effect of filtering all the tickets so that just the ones assigned to me are visible.

Trick 3: Bookmarks and Toggle Buttons

The last piece of the puzzle was to use bookmarks and buttons to switch between the 2 tables. Setting this up can be a bit tricky. The steps I always follow are:

1. Add all the visuals I need to the report
2. Name the visuals in the selection pane so I am clear what each one does. In the image below:
   1. Button Show All
   2. Button Show Mine
   3. All Table
   4. Only Mine Table
   5. User Name Table
   6. Card (showing number of tickets)
      
3. Create the bookmarks
   1. Hide the items not needed, then create the first bookmark
   2. Repeat for the second bookmark
4. Assign the bookmarks to the correct button
5. Test it works as designed
6. Move the objects so they overlay each other
   1. Move button (1) on top of button (2)
   2. Move table (3) on top of table (4)

This is what it looks like (working) before I realign the items.

This is what it looks like after I have overlaid the items on top of each other.

Here is my sample workbook if you want to take a closer look.

Download the sample file here