Forum Discussion
Helper IIFilter by text column and keep rows by ID
Hello and thank you in advance for your help.
I'm working on a report about cooperation between different departments.
I have a table that looks like this
| ID | Project Name | Contributor Name | Department Name |
| 1 | Texas | Adam | D1 |
| 1 | Texas | James | D1 |
| 1 | Texas | Chris | D2 |
| 2 | London | Aric | D3 |
| 2 | London | Adam | D1 |
| 3 | Iceland | Dean | D3 |
| 3 | Iceland | Aric | D3 |
| 4 | Japan | Mick | D4 |
| 4 | Japan | James | D1 |
I wanna filter all the project that have contributors from D1, but keep all the rows of the project.
end result
| ID | Project Name | contributor Name | Department Name |
| 1 | Texas | Adam | D1 |
| 1 | Texas | James | D1 |
| 1 | Texas | Chris | D2 |
| 2 | London | Aric | D3 |
| 2 | London | Adam | D1 |
| 4 | Japan | Mick | D4 |
| 4 | Japan | James | D1 |
My date is huge, over 100,000 projects, 40,000 departments and I can have up to 300 contributors per project.
And I need the option to repeat the filter by different departments.
I'm a little lost, any idea I have increases the file size or takes hours to run.
Please help me.
Hi,
Thanks for the solution rohit1991 offered, and i want to offer some more information for user to refer to.
hello Sharkybu , you can refer to the following solution.
Sample data
Create a new table(Therer is no relationship between the tables.)
Department = SUMMARIZE('Table',[Department Name])Then create a measure.
MEASURE = VAR a = CALCULATETABLE ( VALUES ( 'Table'[Project Name] ), ALLSELECTED ( 'Table' ), 'Table'[Department Name] IN VALUES ( 'Department'[Department Name] ) ) VAR b = CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Project Name] IN a ) RETURN IF ( ISFILTERED ( 'Department'[Department Name] ), IF ( b > 0, 1 ), 1 )Then create a slicer and put the department of the new table to the slicer , and create a table visual, and put the measure to the visual filter.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
rohit1991Super User
Hi Sharkybu ,
Steps (Power Query - Optimized for Large Data):
- Load Data into Power Query → Click Transform Data.
- Duplicate Table → Keep only ID & Department Name.
- Filter for D1 → Keep rows where "Department Name" = D1 → Remove duplicates.
- Merge with Original Table → Left Join on ID.
- Remove Nulls from merged column → Keep only projects containing D1.
- Load Data to Power BI
Dynamic Filtering:
- Use a Parameter instead of "D1" for flexibility.
Performance Tips:
- Use Power Query (not DAX) for efficiencya.
- Remove unnecessary columns.
- Use Table.Buffer() if needed for speed.
SharkybuThank you.
But i need an option the allowes the user to filter the table with a searchable filter.
Hi,
Thanks for the solution rohit1991 offered, and i want to offer some more information for user to refer to.
hello Sharkybu , you can refer to the following solution.
Sample data
Create a new table(Therer is no relationship between the tables.)
Department = SUMMARIZE('Table',[Department Name])Then create a measure.
MEASURE = VAR a = CALCULATETABLE ( VALUES ( 'Table'[Project Name] ), ALLSELECTED ( 'Table' ), 'Table'[Department Name] IN VALUES ( 'Department'[Department Name] ) ) VAR b = CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Project Name] IN a ) RETURN IF ( ISFILTERED ( 'Department'[Department Name] ), IF ( b > 0, 1 ), 1 )Then create a slicer and put the department of the new table to the slicer , and create a table visual, and put the measure to the visual filter.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Sharkybu
Thank you, this helped so much.