How to set filter to show all the rows linked to the filtered item

[Vondox]

Hi,

I would like to ask for an advice. My base data is focused on business conversations and topics discussed within them - there is a great diversity of topics.To be more clear, I prepared an example with animals in the houses below. So I have a list of houses and animalns living in them:

House Animal
1        Dog
1        Cat
1        Parrot
2        Dog
2        Cat
2        Mouse
3        Cat
3        Mouse
3        Parrot

I need to set the filter on animals so when one of the animals is chosen, the output shows all the houses where the filtered animal lives including all the other animals living in those houses.
Example: Show me all the animals living in the house where the dogs live.
The output should look like:

House Animal
1         Dog
1         Cat
1         Parrot
2         Dog
2         Cat
2         Mouse

Do you have any idea how to set the filter or the data in the table in order to get the required result?

Thanks a lot

Lucie

[Chanty4u]

Try this.

Count({<House = P(House)>} Animal)

[Vondox]

Thanks for reply! I tried to use it in my table but does not work. The rows are still filtered to the selected animal only.

[pravinboniface]

Even if you were able to do it, it seems counter-intuitive that filtering for certain animals shows other animals in the same column.

Can you instead create another column with all the animals in the house?  Then when you filter on an animal, you see all other animals in the house.

tab:
load * Inline [
House,Animal
1,Dog
1,Cat
1,Parrot
2,Dog
2,Cat
2,Mouse
3,Cat
3,Mouse
3,Parrot
];
left join (tab) 
load House, Concat(Animal,',') as All_Animals_In_The_House
resident tab
group by House;
exit Script;

[Vondox]

Yes, I have already implemented this workaround before. It's just not that nice and clear, but it's working, so I will probably keep it in my solution. I was just wondering if someone could come up with some out of the box idea 🙂 Thank you!