Forum Discussion
a single filter for two columns
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.
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 __ResultOtherwise, 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 Thanks for the data, see attached PBIX file which basically implements the complex selector I posted originally.
6 Replies
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 __ResultOtherwise, 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.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 nowsajit Can you post the sample data as text?
Greg_Deckler thank you!
