Forum Discussion

Student1's avatar
Student1
New Member
3 years ago
Solved

Add Columns from two table with inactive relationship

Hiya,

 

I am connected to two tables via direct query, and both contain some geographic data. Below sample screenshot shows the tables:

 

 

also, the model is simple like the below:

 

 

My ask is to create a new virtual table with DAX (not PQ), where Table1[zip code] = Tbale2[zip code] and return these columns:
City, lat, long, zip, State and country.

Thanks

Sample file

  • johnt75's avatar
    johnt75
    3 years ago

    You could add a new table like

    Combined table =
GENERATE(
	'Table1',
	SELECTCOLUMNS(
		FILTER( 'Table2', 'Table2'[Zip Code] = 'Table1'[Zip Code] ),
		"State", 'Table2'[State],
		"Country", 'Table2'[Country]
	)
)

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You could add a new table like

    Combined table =
GENERATE(
	'Table1',
	SELECTCOLUMNS(
		FILTER( 'Table2', 'Table2'[Zip Code] = 'Table1'[Zip Code] ),
		"State", 'Table2'[State],
		"Country", 'Table2'[Country]
	)
)