Filter rows of a table based on another visual

[taralouise123]

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 🙂 

[v-kongfanf-msft · Accepted solution]

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 Kong

If 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. 

[v-kongfanf-msft]

Hi @taralouise123 ,

Thanks for your feedback.

Best regards,

Adamk Kong

[bhanu_gautam]

@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.

[taralouise123]

Thanks. 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.

[taralouise123]

Ah yeah- I've added it on but doesn't seem to be working! 

 Measure is:

ShowTasks =

   IF(

       ISFILTERED('ROCC Project Planner'[Project Name]) || ISFILTERED('ROCC Project Planner'[Milestone Fix]),

       1,

       IF(

           MAX('ROCC Project Planner'[Percentage completion]) < 1,

           1,

           0

       )

   )

and filters on the visual are: 

Thanks