Forum Discussion

TheMajesticKing's avatar
TheMajesticKing
Icon for Helper II rankHelper II
6 years ago
Solved

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

Need Help
  • TheMajesticKing's avatar
    TheMajesticKing
    6 years ago

    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]))

19 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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.

    • TheMajesticKing's avatar
      TheMajesticKing
      Icon for Helper II rankHelper II
      6 years ago

      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.

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    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.

    • TheMajesticKing's avatar
      TheMajesticKing
      Icon for Helper II rankHelper II
      6 years ago

      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's avatar
    TheMajesticKing
    Icon for Helper II rankHelper II
    6 years ago

    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.

  • TheMajesticKing's avatar
    TheMajesticKing
    Icon for Helper II rankHelper II
    6 years ago

    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