Show the Tickets Assigned to Me (or Others) - Excelerator BI

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.

Toggle After

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.

Toggle Before

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

Toggle After

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

Download the sample file here

5 thoughts on “Show the Tickets Assigned to Me (or Others)”

  1. If you use USERPRINCIPLENAME() instead it will behave the same way, no matter if you are in PBI Desktop or in the Cloud. It always returns the Mailaddress.

  2. Thanks Matt,
    I can share that I used this solution when was provided in the forum and it works flawlessly in my backlog system. The main challenge with power BI is to keep up with all the changes and finding the nuance solutions that are not brute force settings like RLS. This learning curve keeps curving.

  3. I was at the Power BI booth at Ignite today and was presented with the exact same problem by a lady. I gave her the same solution as you. I wonder if it’s the same person…

  4. …There’s been a USERNAME() function this whole time?!?!? Holy crap! That right there was worth the price of admission, thanks Matt!

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top