a single filter for two columns

[sajit]

i have two columns "assigned to" and "assigned by" and there are common people in both the columns.

i want to have a single filter to filter out both the columns at once. what i mean to say is, let's say joe has 5 tickets "assigned to" him. and he also has 7 other tickets "assigned by" him. so when i filter with the name of joe, i need to to see 12 tickets in total, i.e. both the assigned to and assigned by tickets. how do i achieve this?

ps: i created a new table merging both these columns but I couldn't get a proper relationship between the two tables as there are null values within these columns.

[Greg_Deckler · Accepted solution]

@sajit Normally you would unpivot those columns. You could also do a Complex Selector: The Complex Selector - Microsoft Fabric Community

Like:

Measure =
  VAR __User = MAX('User'[User]) // refer to whatever you are using to pick your user
  VAR __AssignedTo = MAX('Table'[assigned to])
  VAR __AssignedBy = MAX('Table'[assigned by])
  VAR __Result = IF(__AssignedTo = __User || __AssignedBy = __User, 1, 0)
RETURN
  __Result

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[sajit]

@Greg_Deckler thank you!

[Greg_Deckler · Accepted solution]

@sajit Normally you would unpivot those columns. You could also do a Complex Selector: The Complex Selector - Microsoft Fabric Community

Like:

Measure =
  VAR __User = MAX('User'[User]) // refer to whatever you are using to pick your user
  VAR __AssignedTo = MAX('Table'[assigned to])
  VAR __AssignedBy = MAX('Table'[assigned by])
  VAR __Result = IF(__AssignedTo = __User || __AssignedBy = __User, 1, 0)
RETURN
  __Result

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[sajit]

@Greg_Deckler , attached a sample data. "assigned to" and "assigned by" have common people. for example john doe has both tickets assigned by and to him.

and this output i'm looking for, when i filter by the name of john doe. 


The report is in tabular form. I want to add a single filter that filters out data from two columns. the filter should act on both the columns instead of one.

i created a new table in which i combined columns 2 & 3 as a new column "Asignee/Owner" and tried to filter from this column but i couldn't establish a relationship between these tables as there are null values in the columns i want to filter from

please do let me know if it's clear now

[Greg_Deckler]

@sajit Can you post the sample data as text?

[sajit]

@Greg_Deckler , sure. here you go:

Ticket ID	Assigned to	Assigned by	Assigned Group	Product Categorization Tier 1	Product Categorization Tier 2	Submit Date
22987	Scott Stallone	John Doe	Networks	Internal	Research	8/7/2023 19:53
22988	Colin Barnes	Jacob Frey	IT	Internal	Research	7/18/2023 21:39
22989	John Doe	Alex David	IT	Client	Research	5/10/2023 1:46
22990	John Doe	AB Willis	Systems	Internal	Research	1/13/2023 7:33
22991	John Doe	Michael Johnson	Networks	Internal	Research	8/17/2022 8:35
22992	Amy Stiller	Yu Cheung	Cloud	Client	Research	9/21/2023 20:08
22993	David Warner	Amy Stiller	DC	Internal	Logistics	7/12/2023 18:34
22994	James Anderson	John Doe	DC	Internal	Market	5/8/2023 15:45
22995	Colin Barnes	John Doe	Networks	Internal	Market	5/8/2023 15:28
22996	Jacob Frey	Alex David	Systems	Client	Market	4/11/2023 19:54
22997	Patrick Smith	Patrick Smith	Analytics	Internal	Logistics	3/29/2023 12:44

[Greg_Deckler · Accepted solution]

@sajit Thanks for the data, see attached PBIX file which basically implements the complex selector I posted originally.