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
Solved! Go to Solution.
I got it!
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.
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.
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.
I got it!
@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
I hope this is what you are looking for.
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.
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.
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.
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 .
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.
@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.
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.
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
Join the biggest FREE Business Applications Event in LATAM this February.
In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.