topic a single filter for two columns in DAX Commands and Tips

a single filter for two columns

sajit — Thu, 11 Apr 2024 18:51:47 GMT

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.

Re: a single filter for two columns

Greg_Deckler — Thu, 11 Apr 2024 18:57:49 GMT

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.

Re: a single filter for two columns

sajit — Thu, 11 Apr 2024 20:25:28 GMT

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

Re: a single filter for two columns

Greg_Deckler — Thu, 11 Apr 2024 20:35:33 GMT

sajit Can you post the sample data as text?

Re: a single filter for two columns

sajit — Thu, 11 Apr 2024 21:31:50 GMT

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

Re: a single filter for two columns

Greg_Deckler — Thu, 11 Apr 2024 22:06:11 GMT

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

Re: a single filter for two columns

sajit — Thu, 11 Apr 2024 23:19:29 GMT

Greg_Deckler thank you!