Relationship of 3 tables

[vnqtrang]

Hi,

I have 3 tables: 

 1. Country

Country	State	project
US	Minniapolis	Salt
US	Arizona	Sand
US	California	Water
US	Texas	Sky
US	Alaska	Moutain
US	Kentucky	Cloud
Canada	Ontario	Snow
Canada	Quebec	Lake
Canada	Alberta	River

2. Host 

project	Name Host	Group Host	Model	Cost	Capacity	Occupied	Date
Cloud	William	A	Apartment	300	4	2	01/03/2022
Water	Robert	AB	Condo	200	6	5	01/03/2022
Water	Tom	AB	Condo	200	2	0	01/03/2022
Water	Thomas	AC	Single-Family	500	4	0	01/03/2022
Cloud	Donald	B	Tiny Home	200	2	1	01/03/2022
Salt	John	BD	Co-Op	300	2	0	01/03/2022
Salt	Richard	BE	Single-Family	500	4	2	01/03/2022
Lake	Jennifer	C	Tiny Home	300	6	1	01/03/2022
Lake	Karen	D	Townhome	800	2	0	01/03/2022
Water	Rita	DD	Greek Revival	1200	4	0	01/03/2022
Moutain	David	E	Farmhouse	800	2	1	01/03/2022
Moutain	Marc	E	Farmhouse	600	2	0	01/03/2022
River	Anna	G	Townhome	300	4	2	01/03/2022
Sand	James	GF	Craftsman	1000	6	5	01/03/2022
Sand	Joseph	GG	Co-Op	300	2	2	01/03/2022
Sky	Leon	GR	Single-Family	300	4	0	01/03/2022
Sky	Mary	H	Tiny Home	300	2	1	01/03/2022
Snow	Lola	HL	Cape Cod	1500	4	2	01/03/2022
Snow	Lisa	IU	Colonial	3600	10	4	01/03/2022
Sky	Sarah	J	Farmhouse	300	6	5	01/03/2022
Sky	Brad	VG	Colonial	3000	2	0	01/03/2022
Snow	Annie	YI	Contemporary	2000	8	2	01/03/2022
Snow	Patricia	YU	Craftsman	2300	4	1	01/03/2022
River	Nancy	Z	Contemporary	3000	6	0	01/03/2022

3. Subhost

project	Name Host	Group Host	SubHost	Type 1	Type2	Type3	Date
Cloud	William	A	Second (10,866)	1	0	0	01/03/2022
Water	Robert	AB	Third (10,131)	1	0	0	01/03/2022
Water	Tom	AB	First (9,898)	1	0	0	01/03/2022
Water	Thomas	AC	Fourth (9,190)	1	0	0	01/03/2022
Cloud	Donald	B	Park (8,926)	1	0	0	01/03/2022
Salt	John	BD	Fifth (8,186)	1	0	0	01/03/2022
Salt	Richard	BE	Main (7,644)	0	1	0	01/03/2022
Lake	Jennifer	C	Sixth (7,283)	0	1	0	01/03/2022
Lake	Karen	D	Oak (6,946)	0	1	0	01/03/2022
Water	Rita	DD	Seventh (6,377)	0	1	0	01/03/2022
Moutain	David	E	Pine (6,170)	0	1	0	01/03/2022
Moutain	Marc	E	Maple (6,103)	0	1	0

Here is the relation:

I would to make a table that includes almost all the data from 2 tables Host and Subhostinfo. Something like merging the 2 tables below:

I can only add the columns of table Subhostinfo in the first table (below),  when I start to add the column of table Host, I have the error:"Can't determine relationship between fields"

Could you please advise. 

Many thanks in advance.

Tg

[v-yadongf-msft]

Hi @vnqtrang ,

Please create one to one relationship between two tables by column Name Host.

Then you can add columns from Subhost to host.

Or add columns from Host to Subhost.

If you still have questions, please contact us in time.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vnqtrang]

I can't create a 1-1 relationship, cause there are the duplicates

[Syndicate_Admin]

you can create a table with unique values from the column that relates to the host and subhost tables

[amitchandak]

@vnqtrang , You should not connect country and Host (Dimension should not connect) . If you want to filter the host slicer using country , Create a measure like one below and use in visual level filter and check for non blank

countrows(filter(host, host[county] in values(country[country]) ) )

https://www.youtube.com/watch?v=cV5WfaQt6C8