Forum Discussion
Helper IFilter rows of a table based on another visual
Hi,
I want to show of projects/milestones and percentage completion, but only show rows where percentage completion is not 100%- BUT when I click on another visual e.g. a bar chart, it will filter to show the 100% completed tasks as well.
My table looks like this:
Project Name Next Major Milestone Percentage Completion
Planning 01 August 2024 50%
Staff Training 31 July 2024 100%
Skills Calendar 08 August 2024 20%
and my visual I want to filter based on is so I want it to show the 100% completed tasks if these are clicked upon but not show the tasks with 100% completion, if there's no filter.
I'm not even sure if this is possible so any insight is appreciated!
Thanks 🙂
Hi taralouise123 ,
You can try formula like below to create measure:
M1 = IF(MAX(Projects[Percentage Completion]) = 1,1,0)M2 = IF(MAX(Projects[Percentage Completion]) = 1,0,1)result = IF(ISFILTERED(Projects[Project Name]),[M1],[M2])put result to table filter, if not select column in visual, the table visual shows not 100% rows:
after select column about 100%, the result show below:
Best Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
- bhanu_gautam
Super User
taralouise123 , Create a measure for filter first
ShowTasks =IF(ISFILTERED('Table'[Project Name]) || ISFILTERED('Table'[Next Major Milestone]),1,IF(MAX('Table'[Percentage Completion]) < 1,1,0))Select the visual where you want to show the projects/milestones and percentage completion.
Drag the ShowTasks measure to the visual-level filters pane.
Set the filter to show only rows where ShowTasks is 1.
Create Your Visuals:Create the visual that shows the projects/milestones and percentage completion.
Create the bar chart or any other visual that you will use to filter the data.
Test the Interaction:Click on the bar chart or any other visual to apply the filter.
The table visual should now show the 100% completed tasks when a filter is applied.
When no filter is applied, the table visual should exclude the 100% completed tasks.
taralouise123Thanks. Do you mean add it as a visual level filter on the table or the bar chart? When I added it on the table it didn't seem to change anything, but when I added it on the bar chart it removed the 100% completed project, so it's kind of opposite to what I wanted as I wanted it to remove on the table?
Thanks!
- bhanu_gautam
taralouise123 , Add it on table Drag the ShowTasks measure to the visual-level filters pane.
Set the filter to show only where ShowTasks is 1.
Hi taralouise123 ,
You can try formula like below to create measure:
M1 = IF(MAX(Projects[Percentage Completion]) = 1,1,0)M2 = IF(MAX(Projects[Percentage Completion]) = 1,0,1)result = IF(ISFILTERED(Projects[Project Name]),[M1],[M2])put result to table filter, if not select column in visual, the table visual shows not 100% rows:
after select column about 100%, the result show below:
Best Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- taralouise123
Thank you! This has helped. It's not filtered my table completely but it has removed the 100% completed tasks and then adding percentages in my project category table has helped with being able to drill through onto the 100% completed tasks anyway, so it's solved my issue, thanks.
