Filter Out Same-Value Data in One Column, When a Value in Another Column Is Not Present

[ClackamasAshley]

Hello,

I don't even know what words to use to call, or describe what I need to do, let alone search for a solution appropriately, so I am trying to explain it as clearly and simplistically as possible.

I have law enforcement call data, which in its simplest form is made up of the "Call Number", "Deputy Name," and their "Unit Role." I need to keep, and analyze all the deputies that were assigned to the same calls as one specific deputy (Casey, in my example). I need to do this by filtering out all the calls that Casey was NOT assigned to. However, if I just use a filter to filter for Casey's calls, I will filter out all the other deputies that were assigned to the same calls Casey was assigned too, and those are the rows of data that I actually need for this analysis.

I am trying to answer the questions, "Which deputies were assigned as "Primary" to the calls Casey was also assigned to, and how many times?"

How can I isolate my data so I am just working with the call numbers and all their rows, that Casey was assigned to so I can further analyze what other deputies were assigned to those calls and their roles??

Here is a screenshot of the sample data to illustrate what I have, and what I am trying to do with it. Any help would be so greatly appreciated! Thank you!

Ashley

Call Number	Deputy Name	Deputy Role	What to Do with Each Call Number
3476890	Casey	Primary	Keep
3476890	Joe	Additional
3476890	Megan	Additional
3476890	Tim	Additional
3476891	Megan	Primary	Filter Out
3476891	Renae	Additional
3476892	Casey	Primary	Keep
3476892	Adam	Additional
3476892	Joe	Additional
3476892	Megan	Additional
3476892	Renae	Additional
3476893	Renae	Primary	Keep
3476893	Casey	Additional
3476894	Tim	Primary	Filter Out
3476894	Adam	Additional
3476894	Joe	Additional
3476895	Joe	Primary	Keep
3476895	Adam	Additional
3476895	Casey	Additional
3476895	Renae	Additional
3476895	Tim	Additional
3476896	Renae	Primary	Filter Out
3476896	Adam	Additional
3476896	Megan	Additional
3476897	Joe	Primary	Filter Out
3476897	Renae	Additional
3476897	Tim	Additional
3476898	Joe	Primary	Keep
3476898	Casey	Additional

[tzvetkov_b87]

Hi @ClackamasAshley ,

I create a Power BI file and uploaded it here for you to have a look.

The way I have done it is:

I am assuming it will not always be Casey you want to select so it is dynamic.

I created a new table called 'Deputies'. This doesn't have an Active relationship, because we don't want it to filter the Callouts.

I've added a slicer from the 'Deputies' table to allow us to select just one Deputy.

We then create a measure that does the following:

1. see who is the selected deputy

2. get the current case number (as this measure will be displayed on a table, each row will have its own case number)

3. create table with all the distinct case numbers that the selected deputy has worked on

4. check if the current case number is in the table of all cases that the deputy has worked on.

Hope this helps out - and if anybody has a quicker or better way of doing it let me know. 

If I helped, please mark my post as the answer and a kudos will be very much appreciated 🙂

Merry Christmas and a Happy New Year !

Boyan