How to filter table data by clicking on the bar in Clustered Column chart

[LynnW]

I have this table:

Item	Category	Status
Item 1	Category 1	In backlog
Item 2	Category 1	In backlog
Item 3	Category 1	In backlog
Item 4	Category 1	In backlog
Item 5	Category 1	In analysis
Item 6	Category 1	In analysis
Item 7	Category 1	In analysis
Item 8	Category 1	In analysis
Item 9	Category 1	In analysis
Item 10	Category 1	In analysis
Item 11	Category 1	In dev
Item 12	Category 1	In dev
Item 13	Category 1	In dev
Item 14	Category 1	In dev
Item 15	Category 1	In dev
Item 16	Category 1	In dev
Item 17	Category 1	In dev
Item 18	Category 1	In dev
Item 19	Category 1	In qa
Item 20	Category 1	In qa
Item 21	Category 1	In qa
Item 22	Category 1	In qa
Item 23	Category 1	In qa
Item 24	Category 1	In qa
Item 25	Category 1	In qa
Item 26	Category 1	In qa
Item 27	Category 1	In qa
Item 28	Category 1	Done
Item 29	Category 1	Done
Item 30	Category 1	Done
Item 31	Category 2	In backlog
Item 32	Category 2	In backlog
Item 33	Category 2	In backlog
Item 34	Category 2	In backlog
Item 35	Category 2	In backlog
Item 36	Category 2	In backlog
Item 37	Category 2	In backlog
Item 38	Category 2	In backlog
Item 39	Category 2	In analysis
Item 40	Category 2	In analysis
Item 41	Category 2	In analysis
Item 42	Category 2	In analysis
Item 43	Category 2	In analysis
Item 44	Category 2	In analysis
Item 45	Category 2	In analysis
Item 46	Category 2	In dev
Item 47	Category 2	In dev
Item 48	Category 2	In dev
Item 49	Category 2	In dev
Item 50	Category 2	In dev
Item 51	Category 2	In dev
Item 52	Category 2	In qa
Item 53	Category 2	In qa
Item 54	Category 2	In qa
Item 55	Category 2	In qa
Item 56	Category 2	Done
Item 57	Category 2	Done
Item 58	Category 2	Done
Item 59	Category 2	Done
Item 60	Category 2	Done

And also created new measures calcuate by status: 

New measure	Count by Status
To do	In backlog
Pening	In analysis
In Progress	In dev & In qa
Completed	Done

And built this dashboard:

I'm able to click on the categery, so the table data is filtered only keep the items for category.

What I want to do is when click on each of the status bar in each category,  data in table should be filtered out. 

For example if I click on 'In progress' bar in 'Category 1', the table should show all the items that are in category and have status either 'In dev' or 'In qa'. 

Do any one know how to solove this issue?

https://drive.google.com/file/d/1JXhsQZYafcqcVrJupW3UQIXBVOznWWpA/view?usp=drivesdk

[rajendraongole1 · Accepted solution]

Hi @LynnW  - create a calculated column that maps each Status to your new bucket. When you click on In Progress, Power BI will filter the table correctly (to In dev and In qa), because the filter context flows from the column. 

eg: Status Bucket =
SWITCH(
TRUE(),
'Table'[Status] = "In backlog", "To Do",
'Table'[Status] = "In analysis", "Pending",
'Table'[Status] IN { "In dev", "In qa" }, "In Progress",
'Table'[Status] = "Done", "Completed",
BLANK()
)

Hope this works, please try.

[v-venuppu]

Hi @LynnW ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @LynnW ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MasonMA @rajendraongole1 for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[MasonMA · Accepted solution]

Hi, 

Power BI does not automatically filter visuals based on measures unless those measures are tied to actual column values. Your chart uses custom measures, so clicking a bar like "In Progress" doesn’t pass a filter contexts to the table.

There is another way with creating a column in table. 

Group = 
SWITCH(
    TRUE(),
    Sheet2[Status] IN {"In dev", "In qa"}, "In Progress",
    Sheet2[Status] = "In backlog", "To Do",
    Sheet2[Status] = "In analysis", "Pending",
    Sheet2[Status] = "Done", "Completed"
)

Then use this new column on Legend and count of this column on Y-axis. 

[rajendraongole1 · Accepted solution]

Hi @LynnW  - create a calculated column that maps each Status to your new bucket. When you click on In Progress, Power BI will filter the table correctly (to In dev and In qa), because the filter context flows from the column. 

eg: Status Bucket =
SWITCH(
TRUE(),
'Table'[Status] = "In backlog", "To Do",
'Table'[Status] = "In analysis", "Pending",
'Table'[Status] IN { "In dev", "In qa" }, "In Progress",
'Table'[Status] = "Done", "Completed",
BLANK()
)

Hope this works, please try.