How to use a slicer with two columns

[Sachintha]

My question boils down to (I think) the topic, but let me describe my issue in detail. I have a PBI report based on two different SharePoint lists.

First one, let's call it [ Tickets ]. Company employees create tickets in there, which has many columns, but the ones of interest to us are the four columns below:

[ ID, Date, Manager, Employee ]

I have a separate list, called [ TrackedManagers ] which is essentially a subset of the managers. It only has one column:

[ Manager ]

I'm including sample data lists for both lists at the end of this post.

One important thing to remember about the Tickets list is both regular employees and managers can submit tickets. So, a manager can be in the [ Manager ] column as well as the [ Employee ] column, although never in the same row.

The purpose of the report is to analyze ticket data for the tracked managers. I want to analyze this data in two separate ways, using matrix visuals.

1. Tickets submitted by the tracked managers.
2. Tickets submitted by the employees reporting to the tracked managers.

First visual should look like below. The 'employee' column names are the 'Tracked Managers' list. These are the ticket submitted by those managers.

Second visual, I need to see the tickets submitted by employees reporting to those tracked managers.

I can obviously do this (as I have done to produce above screenshots) by manually selecting those managers. But waht I really want to do is, have a single slicer (this is VERY important), which should list only the tracked managers, and when the user selects the managers from there, the first chart should display their ticket numbers (if they have submitted any), and the second chart should display tickets submitted by employees reporting to them (if they have any).

What's the best way to do this?

Tickets:

Id,Date,Employee,Manager
23057,2023-09-27,Justin,Eric
23058,2023-09-27,David,Duane
23059,2023-09-27,David,Duane
23060,2023-09-27,Anthony,Kevin
23061,2023-09-27,David,Duane
23062,2023-09-27,Rick,Kurt
23063,2023-09-27,Kieron,Shane
23064,2023-09-27,Jared,Kurt
23065,2023-09-27,Chris,Anthony
23066,2023-09-27,Drew,Chris
23067,2023-09-27,James,Kevin
23069,2023-09-27,Michael,Scott
23070,2023-09-27,Micah,Scott
23071,2023-09-27,Isaias,Kurt
23072,2023-09-27,Chad,Eric
23047,2023-09-26,Chris,Jason
23048,2023-09-26,Dennis,Shane
23049,2023-09-26,Jose,Matt
23050,2023-09-26,Philip,Howard
23051,2023-09-26,Lamont,Kevin
23052,2023-09-26,Aaron,Chris
23053,2023-09-26,Alayna,Casey
23054,2023-09-26,David,Duane
23055,2023-09-26,Emmett,Anthony
23056,2023-09-26,Emmett,Anthony
23033,2023-09-25,Daniel,Chris
23034,2023-09-25,Andrew,Anthony
23035,2023-09-25,Raul,Eric
23036,2023-09-25,David,Duane
23037,2023-09-25,Anthony,Kevin
23038,2023-09-25,Tom,Jason
23039,2023-09-25,James,Duane
23040,2023-09-25,Jason,David
23041,2023-09-25,Rick,Kurt
23042,2023-09-25,Jason,David
23043,2023-09-25,Isaias,Kurt
23044,2023-09-25,Isaias,Kurt
23045,2023-09-25,James,Jason
23046,2023-09-25,Leonides,David
23031,2023-09-24,Raul,Kevin
23032,2023-09-24,Kyler,Shane

TrackedManagers:

Manager
Anthony
David
Duane
Jason
Kevin
Kurt
Scott

[CoreyP · Accepted solution]

I just created a 2nd table called manager tickets. I went into PQ, Merged Queries as New > Tracked Managers to Tickets, joined on Employee column. Filtered that new query where Manager.1 was not null. Then created a relationship from tracked managers to manager.1 ( on manager tickets ), and tracked managers to manager ( on tickets ). Then use the tracked managers table as your slicer. 

[Sachintha]

@CoreyP thank you.

This seems to work for the second matrix, but not for the first one.

So, my relationships are:

TrackedManagers.Manager = Tickets.Manager
TrackedManagers.Manager = ManagerTickets.Manager.1

For my 2nd table, my rows are as follows:

Rows:

TrackedManagers.Manager
Tickets.Employee

Then, in 'Filters on this visual' I set [TrackedManAnd this works fine.

However, what do I set as the Columns Rows for the first table?

[CoreyP]

[Sachintha]

OK, so the issue was I had this in reverse:

Merged Queries as New > Tracked Managers to Tickets

Funnily, if I do that, though, I can get the result even withou Manager.1 column as mentioned in my other reply.

[Sachintha]

Actually I figured out the problem. The relationship should be:

Tickets.Manager = TrackedManagers.manager

ManagerTickets.Manager = TrackedManagers.Manager

I don't even need the Manager.1 column, that gives me the undesired result.

[CoreyP]

Ohh, I might have merged my queries in a different order than how I wrote. Like Tickets > Managers, not Managers > Tickets. My bad.