Join columns from two unrelated tables based on multiple common columns in DAX

[Anonymous]

Hi team,

I need to implement below requirement in DAX.

I have two tables like below. Both the tables are not related to each other and they are not related to any common tables(Sample tables below)

Table 1                                                         

pdt	cst	Acv
P1	C1	0.1
P2	C2	1.1
P3	C3	2.1
P4	C4	3.1
P5	C5	4.1

Table 2

pdt	cst	tgt
P1	C1	2.2
P2	C2	3.2
P3	C3	4.2
P4	C4	5.2
P5	C5	6.2

The tables have multiple common columns.I want to create a calculated table which will have the columns from two tables which are joined based on the multiple common columns between them something like below(Similar to inner join in SQL)

pdt	cst	Acv	tgt
P1	C1	0.1	2.2
P2	C2	1.1	3.2
P3	C3	2.1	4.2
P4	C4	3.1	5.2
P5	C5	4.1	6.2

The join should be based on both pdt and cst columns.

I tried using Summarize with Union and Treatas options but no luck.

Can this be done.Please suggest.

Thanks,

Sam

[Anonymous]

Thanks for taking time to reply @V-pazhen-msft / @amitchandak . I used treatas with NaturalInnerJoin and it worked.

Thanks,

Sam

[V-pazhen-msft]

@Anonymous 

Is there any reason you cannot not have an active relationship between the tables. You could create this relationship between the [pdt] columns and leave it as Inactive relationship (dotted line). Then create the following measure:

Measure = CALCULATE(SUM('Table (2)'[tgt]),USERELATIONSHIP('Table'[pdt],'Table (2)'[pdt]),FILTER('Table (2)',[pdt]=MAX('Table'[pdt])))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , you can create one/two common dimensions of pdt and cst. and join with both the tables and analyze them.

If need you can create a combine key.

Key = [pdt] & "-" & [cst]

refer

https://www.knowledgehut.com/blog/business-intelligence-and-visualization/power-bi-best-practices

[Anonymous]

@amitchandak ..I have many columns in actual tables and I cannot create new tables.Also I dont have any of these columns in the visual, but I require these columns in one table to perform further analysis.