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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TheMajesticKing
Helper II
Helper II

How to make an interaction ignore a visualization-level filter

I'm having a hard time trying to figure out how to explain what I'm trying to do but here goes:

I have two visuals. A Campus "Map" visual and a "Room" visual. The Map visual shows a bunch of buildings on a map. The Room visual shows the number of active users in each room. I have a filter set on the Room visual to show only rooms that have more than 0 active users.

The two visuals are linked by room so clicking on a room will highlight the building where it's located and clicking on a building  lists the rooms and the number of active users for that room. 

Here's what I want:
When I click on a building, I need the room visual to ignore the "Active Users Great Than 0" filter and show all rooms that are in that building regardless of whether or not it is has any active users. Is this possible?

Here's a link to the sample file: https://drive.google.com/file/d/1MEZAb7WoUGLUf-hjWC54T8XpF0HK46GK/view?usp=sharing

1 ACCEPTED SOLUTION

I got it!

 

Active Users = if(ISFILTERED(Table1[Building]), IF(CALCULATE(SUM(Table1[Active])) = BLANK(), CALCULATE(0, ALL(Table2[Room])), CALCULATE(SUM(Table1[Active]))), SUMX(FILTER(Table1, Table1[Active] > BLANK()),Table1[ActiveUserWithZero]))

View solution in original post

19 REPLIES 19
TheMajesticKing
Helper II
Helper II

daily bump

@TheMajesticKing can you send the pbix file. Remove any sensitive information before sharing it.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@TheMajesticKing I'm confused, when I clicked H it is showing 3 rooms, what actually is the issue?

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Yeah it's difficult to explain. Maybe I'll try to explain it in long form.

The visual that I want to manipulate is the bar graph (Active Staff By Room) on the right side of the report.

As you can see in your screenshot, there are rooms listed with zero active users. I don't want to see those rooms unless someone clicks on a building in the pie chart. When I click on a building in the pie chart (Active by Building) visual I want the Bar chart to show all of the rooms in that building regardless of whether or not it has 0 active users.

In other words I want to make it so the "Active Staff By Room" visual filters out the rooms with no active users when there is no active crossfiltering but then I want it to "unfilter" whenver a building is clicked in the "Active By Building Visual". I'll attach two screen shots to further explain what I want to see. powerbi-2.PNGpowerbi-1.PNG

That's not what I'm asking. I'm already familiar with the filter button. That's what I used to generate the screenshot. Please read my previous reply entirely. I'm not just asking to filter out the zeros all the time. I want the zeros there when no data is being crossfiltered.

@TheMajesticKing if your screenshot is showing zero room, what we are missing. I'm surely missing the point here. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I really appreciate your help but it seems that you're not understanding what I need. I've explained it 5 different ways with screenshots and everything so I'm not sure what else to do. I'd love for us to get this sorted out. If you have some time, please read back through all of my previous replies and you might get a better idea of what i want. The screenshots that I attached are two different scenarios. In order to generate those screenshots I had to manually filter and unfilter the zeros from the bar chart. I want this to happen automatically. I told you it was hard to explain. It's not a simple thing. lol

@TheMajesticKing nothing is simple until it sorted out. let's book a time over zoom and discuss it thru screen sharing if you are open to it.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I got it!

 

Active Users = if(ISFILTERED(Table1[Building]), IF(CALCULATE(SUM(Table1[Active])) = BLANK(), CALCULATE(0, ALL(Table2[Room])), CALCULATE(SUM(Table1[Active]))), SUMX(FILTER(Table1, Table1[Active] > BLANK()),Table1[ActiveUserWithZero]))

@TheMajesticKing omg, you made is super complicated and it is very simple things to do, you just need to change the interaction between your center visual with right visual from cross highlight to cross filter.

 

- go to format tab

- click edit interactions

- select center visual

- on right visual, click filter icon instead of cross highlight and that will do it

 

image.png

 

I hope this is what you are looking for.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@TheMajesticKing here is the post how it works

 

I would 💖 Kudos 🙂 if my solution helped. If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

TheMajesticKing
Helper II
Helper II

bump

TheMajesticKing
Helper II
Helper II

I just made a sample pbix. As you can see there are multiple rooms per building and some rooms are empty. I'm trying to accomplish 2 things:

1. (This part is easy) I want to filter the "Active Staff Per Room" visual so that when no visuals have been clicked, it only shows rooms with active users. In other words, rooms with "0" active users should be filtered.

BUT

2. When I click on one of the buildings in the Pie Chart I want the Active Staff visual to show All Rooms in that building even if the room has "0" active users. For example, if I click on building "H", the Active Staff visual should show should Rooms 11, 3, and 2, even though Room 2 has 0 active users. 

Link to file: https://drive.google.com/file/d/1MEZAb7WoUGLUf-hjWC54T8XpF0HK46GK/view?usp=sharing

 

PowerBI-Map-Help.PNG

TheMajesticKing
Helper II
Helper II

Here's the measure I created but I can't get it to actually filter. I'm likely using it wrong. I imagine I should be using HASONEVALUE here but I'm not sure how to use it:

 

MEASURE = IF(ISCROSSFILTERED(ActiveUsers[Room]), ActiveUsers[ActiveCount], SUMX(FILTER(ActiveUsers, ActiveUsers[Active] > 0, [Active]))

 

Let me again try to explain what I want to do. I want the visual to show all rows with more than 0 active users. However, if I click on a building on the "Map" visual it will filter this visual by "Room" but I want it to show the rooms with 0 active users whenever I click on the building. So clicking on the map visual needs to show all rooms whether or not they have 0 active users.

v-xicai
Community Support
Community Support

Hi @TheMajesticKing , 

 

If you need to choose which visuals to be filtered by one special visual or not, then try to use "Edit interactions" feature, see more details: https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions.

 

There is a similar case you can refer to : Table Filter Interactions .

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I'm aware of how the visual filter buttons work. However, I'm trying to "Unfilter" the "Room" Visual whenever I click a building in the "Map" visual.

parry2k
Super User
Super User

@TheMajesticKing Yes it is possible by using HASONEVALUE function on your building table and in your measure check the condition and create your measure accordingly. If need more details, please provide a sample pbix and will get you the solution. Please remove sensitive information before sharing.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks for your help. I've attempted to create a sample pbix but it's difficult considering the whole thing is a map that I can't share and might take too much time considering I'm still pretty new to PowerBI.

I actually didn't explain my tables correctly. I have 2 Visuals (Map and Rooms) but they are pretty much sharing 1 table. The table contains a list of active users, rooms, and buildings. So both visuals are pulling from the same table. 

I'm sort of having a hard time understading HASONEVALUE. I tried creating a HASONEVALUE measure for building and I tried creating one for Room but I'm not quite sure which one to use or where to use it. Or should it be for Active Users? Could you give me an example of a measure that would work for this or give me an idea of what you had in mind?

Thanks again for you help.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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