Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
clarkbj71
Helper I
Helper I

Cross-Filtering between bar chart and table using measures in bar chart

I needed to have the legend on my chart that does not change based on report filtering so I created measures to calculate the count of ID’s for each Status.  This allowed the legend to stay static no matter what filters are applied to the report page.  The problem I now have is that when I select one of the counts in the bar chart (i.e. Actions Complete for DB1), it no longer filters the table with the same data.  So the measures I created are not allowing any cross-filtering to the table.  One of the measures I created was:

 

Count of Id for Actions complete =

CALCULATE(COUNTA('Case'[Id]), 'Case'[Status] IN { "Actions complete" })

 

And I have 7 other measures with different status’s.

 

The Bar chart has Type for the Axis (which is DB1 and DB2), and each of the measures for values.

 

The Table shows Type, Status, and a bunch of other values.

 

Is there a better way to make sure the legend doesn’t change based on filtering or is there a better measure I can write that allows cross-filtering to the table?

1 ACCEPTED SOLUTION

Not sure why you would want to confuse your users this way but here's an implementation

 

 

View solution in original post

6 REPLIES 6
clarkbj71
Helper I
Helper I

I agree this isn't ideal but this is what the customer wants... a static legend so they know what other status's are available.  Your suggestion is a good one.  I will take that to them as a solution.  Thanks! 

lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.


Sorry for the late reply.  Here is a sample of the data.  This sample is also what would be shown in the Table Graph.  The picture of the bar chart is included.  If I use measures for each status in the bar chart, It no longer filters the table chart.  For instance, if I select "Completed" in the bar chart, the table graph still shows all status's (does not filter just the ones that are "Completed").  I need the legend to stay static so on the main page filters, if someone selects location = MB, the legend in the bar chart stays the same.  

TypeStatusCaseCreated DateLocationID
RoundNew1344937/25/2019 18:22MB12aTgQAI
RoundComplete1344947/25/2019 18:22MB12aTRQAY
RoundComplete1426188/16/2019 0:36B1fuIqQAI
RoundUnder Investigation16765010/10/2019 5:17A3VhnbQAC
RoundComplete17093510/17/2019 8:17A3jWqEQAU
RoundNew17225910/20/2019 21:12MB3lr9uQAA
RoundActions complete2918205/12/2020 4:45SA9UxwKQAS
RoundAwaiting closure2958855/19/2020 2:37SA9jMTwQAM
ChecklistComplete40999311/21/2020 4:39CDpj1gQAB
ChecklistNew4468991/15/2021 10:25WF1gu9QAB
ChecklistComplete4517791/23/2021 18:04S1F47vvQAB
ChecklistComplete4517801/23/2021 18:04S1F47w4QAB
ChecklistNew5163315/8/2021 9:13WHcbg5QAB
ChecklistComplete5284555/29/2021 22:55S29ECdrQAG
ChecklistAwaiting closure67105612/3/2021 14:54GMnkbkQAB
ChecklistUnder Investigation67137612/4/2021 6:03GMnspKQAR

clarkbj71_0-1647529232782.png

 



What is your expected outcome?  Here is an example that doesn't use measures at all

 

lbendlin_1-1647539962794.png

 

How is this insufficient for what you are trying to achieve?  How do you expect users to interact with the visual?

 

 

If you have a slicer on the page for Location and you select G, I want the legend to still show everything.  Creating measures allows this to happen but when I then add a table of the data to the page and when I select one of the bars (completed), the table is not filtering to just show completed.  That is the consequence I found with the measures.  It fixes one problem (keeping legend intact) but does not solve the cross filtering function between the table and the bar chart.   Maybe the measures need to be written differently or maybe there is no fix that will keep legend intact and allow the cross-filtering.

Not sure why you would want to confuse your users this way but here's an implementation

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.