Use Filter or Single Selection to show all related data in parent group

[drewmcspy]

Hi

The below table is a fictional list of cities, people and age.

City	Name	Age
Liverpool	Tom	30
Liverpool	Jill	15
Liverpool	Jim	26
Liverpool	Sophie	32
Liverpool	Lynsey	20
Liverpool	Helen	33
Liverpool	Mary	20
Liverpool	Jen	31
New York	Helen	22
New York	Mary	15
New York	Tom	24
New York	Jon	26
La Paz	Bren	24
La Paz	Mel	33
La Paz	Helen	25
La Paz	Ruby	22
La Paz	Jim	20
Beijing	Jill	29
Freetown	Bren	30
Durban	Sarah	16
Durban	Sophie	30
Madrid	Jill	16
Madrid	Tom	31
Madrid	Mary	15
Madrid	Helen	24
Madrid	Jon	28
Madrid	Mark	30

What I'm hoping to do is, when a name is selected, it Power BI will show all groups they belong to and all other members of the groups, including their ages. 

Examples

If Tom is Selected

City	Name	Age
Liverpool	Tom	30
Liverpool	Jill	15
Liverpool	Jim	26
Liverpool	Jon	32
Liverpool	Lynsey	20
Liverpool	Helen	33
Liverpool	Mary	20
Liverpool	Jen	31
New York	Helen	22
New York	Mary	15
New York	Tom	24
New York	Jon	26
Madrid	Jill	16
Madrid	Tom	31
Madrid	Mary	15
Madrid	Helen	24
Madrid	Jon	28
Madrid	Mark	30

If Jill is Selected

City	Name	Age
Liverpool	Tom	30
Liverpool	Jill	15
Liverpool	Jim	26
Liverpool	Jon	32
Liverpool	Lynsey	20
Liverpool	Helen	33
Liverpool	Mary	20
Liverpool	Jen	31
Beijing	Jill	29
Madrid	Jill	16
Madrid	Tom	31
Madrid	Mary	15
Madrid	Helen	24
Madrid	Jon	28
Madrid	Mark	30

If Sophie is Selected

City	Name	Age
Durban	Sarah	16
Durban	Sophie	30

I hope that makes sense and hope it's achievable.

Thanks in advance.

mcspy

[barritown · Accepted solution]

Hi @drewmcspy, 

If by groups you mean cities and your dataset is relatively small, you could use such a solution:

1) Create a full copy of your table.

2) Link the original table and the duplicate via [City] (many-to-many).

3) In the slicer, you could use the duplicate table, in the table visual - the orinal one.

BTW, the "Sophie" test case is not quite correct - there is another Sophie in Liverpool.

Here is the scheme:

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[drewmcspy]

Exactly what I needed, thanks

(completely right about Sophie too 😀)