How to join to Calculated table.

[Rahul66303]

Hi,

Need help on below scenario :

I have two calculated tables :

CoverageTable
ServiceOffering	ServiceLine	Capability	BusinessCapabilities	CoverageValue
ServiceOffering1	ServiceLine1	Capability1	Tag1	3
ServiceOffering1	ServiceLine2	Capability1	Tag1	2
ServiceOffering1	ServiceLine3	Capability1	Tag1	2
ServiceOffering1	ServiceLine2	Capability2	Tag1	1
ServiceOffering1	ServiceLine3	Capability2	Tag1	1
ServiceOffering1	ServiceLine2	Capability3	Tag1	1
ServiceOffering1	ServiceLine3	Capability3	Tag1	1
ServiceOffering2	ServiceLine4	Capability4	Tag1	1

Another Calculated Table :

BaselineTable
ServiceOffering	ServiceLine	Capability	BusinessCapabilities	BaselineValue
ServiceOffering1	ServiceLine1	Capability1	Tag1	5
ServiceOffering1	ServiceLine2	Capability1	Tag1	3
ServiceOffering1	ServiceLine2	Capability1	Tag2	3
ServiceOffering1	ServiceLine2	Capability1	Tag3	8
ServiceOffering1	ServiceLine2	Capability1	Tag4	2
ServiceOffering1	ServiceLine3	Capability1	Tag1	3
ServiceOffering1	ServiceLine3	Capability1	Tag2	3
ServiceOffering1	ServiceLine3	Capability1	Tag3	8

What i need to join above two tables and result table as calculated table :

CoverageVsBaseLineTable
	ServiceOffering	ServiceLine	Capability	BusinessCapabilities	CoverageValue	BaselineValue
	ServiceOffering1	ServiceLine1	Capability1	Tag1	3	5
	ServiceOffering1	ServiceLine2	Capability1	Tag1	2	3
	ServiceOffering1	ServiceLine3	Capability1	Tag1	2	3
	ServiceOffering1	ServiceLine2	Capability2	Tag1	1	8

[Anonymous]

Hi @Rahul66303,

The first table is missing only the Baseline value. You could create a column and using Lookup get the Baseline value. Do you have a primary key for the 2 tables?

[Rahul66303]

Hi @Anonymous

These two tables are calculated tables from different tables and calculations, i do n't have primary key on these calculated tables ?can i create primary key on calculated tables ?

[Anonymous]

Hi @Rahul66303,

You can create one. Create a column key in both the tables

Key = ServiceOffering & ServiceLine & Capability & BusinessCapabilities

Now create a column Baseline in the first table

Baseline = Lookup(Table2[Baseline], Table2[Key], Table1[Key])

Let me know if this does not work.

[Anonymous · Accepted solution]

Hi @Rahul66303,

Please mark the solution as accepted if it worked for you.

Thank you 🙂