Stuck with Relationships - Can't work it out - New to Power BI

[NiugeS]

Hi guys - i've been at this for several days trying to work it out.  Been in and out of multiple youtube videos but not sure what i'm doing wrong or what I need to do.  Likely something very straight forward but can't get my head around it. 

In short, I have 4 tables and tried to recreate what i'm trying to do with images below as well as an example pbix.  The four tables are Location, People, People Links and Cars.

I want to have a slicer to when a location is picked, it displays the relevant parent and child(s) and what cars they have. 

Location	Parent	Child	Car
Prince Tishroyd	Vanesa Cobb	Libbie Magana	Bmw R1100S ABS
Prince Tishroyd	Vanesa Cobb	Shanon Leech	Chevrolet SONIC

or like this if Prince Pointal is picked

Location	Parent	Child	Car
Prince Pointal	Rubie Townsend	Om Newman	Nissan ARMADA

I'm honestly stumped and super greatful for any help...    tables below and pbix attached.. Family Test Pbix  

Location
City	ID
Prince Tishroyd	B1001
San Raytroyphi	B1001
Cape Brierfegib	B1001
Prince Pointal	C1001
Grand Thoubewd	C1001
Baliadsor	E1001
Moose Xmundmiles	E1001

People
ID	Type	Name
A1001	Child	Libbie Magana
D1001	Child	Shanon Leech
J1001	Child	Om Newman
K1001	Child	Shanay Harris
L1001	Child	Olivier Stott
M1001	Child	Alastair Roth
Q1001	Child	Millie-Rose Mcguire
B1001	Parent	Vanesa Cobb
C1001	Parent	Rubie Townsend
E1001	Parent	Sol Winter

People Link
ID	Linked: ID	Linked Issue Direction
B1001	A1001	Outward
B1001	D1001	Outward
A1001	B1001	Inward
D1001	B1001	Inward
C1001	J1001	Outward
J1001	C1001	Inward
E1001	K1001	Outward
E1001	L1001	Outward
E1001	M1001	Outward
E1001	Q1001	Outward
K1001	E1001	Inward
L1001	E1001	Inward
M1001	E1001	Inward
Q1001	E1001	Inward

ID	Car
A1001	Bmw R1100S ABS
D1001	Chevrolet SONIC
J1001	Nissan ARMADA
K1001	Arctic Cat M 6000 SNO PRO
L1001	Arctic Cat M1000 EFI 162
M1001	Arctic Cat ZR 6000 EL
Q1001	Honda CRF50F

Thank you

[vivran22 · Accepted solution]

Hello @NiugeS 

You may use Power Query for this:

Solution pbix file

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[NiugeS]

@vivran22  Thank you so much.  I couldn't work that out.

As the location table is quite large with mulitple columns, can this still be achieved without merging the two?  Many thanks for all your help.

[vivran22]

Hello @NiugeS 

What is the source of the data table? Is it SQL? You may write queries and bring less tables in Power BI (2 instead of 4). That is a better approach.

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn