Getting a Circular Dependency Error in relationship when using 2 concatenated Columns of 2 tables

[Anonymous]

Hello Everyone,

I have two tables and I am trying to create a relationship between these two tables with the best possible scenario.

Please refer to the tables below:

Table 1						Table 2
Vendor Name	Year	Month	Conc_A	Conc_B		Vendor Name	Year	Month	Conc_C	Conc_D
ABC	2020	Jan	ABC2020Jan	ABC2020Jan		ABC	2020	Jan	ABC2020Jan	ABC2020Jan
CDE	2020	Feb	CDE2020Feb	CDE2020Feb		CDE	2020	Feb	CDE2020Feb	CDE2020Feb
EDF	2020	March	EDF2020March	EDF2020March		EDF	2020	March	EDF2020March	EDF2020March
HGH	2021	Apr	HGH2021Apr	HGH2021Apr		HGH	2021	Apr	HGH2021Apr	HGH2021Apr
HGH	2021	April	HGH2021April	HGH2021April		HGH	2021	April	HGH2021April	HGH2021April
HGH	2021	May	HGH2021May	HGH2021		HGH	2021	Jan	HGH2021Jan	HGH2021
HGH	2021	May	HGH2021May	HGH2021		HGH	2021	Feb	HGH2021Feb	HGH2021
HGH	2021	Jun	HGH2021Jun	HGH2021		HGH	2021	Mar	HGH2021Mar	HGH2021
HGH	2021	June	HGH2021June	HGH2021		HGH	2021	Apr	HGH2021Apr	HGH2021

a. First I created a Custom Column in each table concatenating Vendor Name, Year and Month. In table 1 its Conc_A, In Table 2 its Conc_C. However two rows wont get connected because in Table 2, Row 3 and Row 4 have a different Month.

b. So I created one more Custom Column in each table using Lookup Dax to use Vendor Name, Year and Month concatenation where ever this combination matches between two tables and where it doesnt match just concatenate Vendor Name and Year. So I created Conc_B and Conc_D with the best possible scenario where it matches all rows. Now when I want to create a relationship between these two table I am getting a Circular Dependency Error. 

Can someone please help in understanding this error and what is causing the Circualar Dependency error and how do we rectify it

The screenshots below are from the actual data I am working on

Regards,

Vamshi Regalla 

[Anonymous · Accepted solution]

Hello All,

I Figured it out. The reason it was showing a Circular dependency is because the combination in Column Conc_D was repeating. 

To address this I created a Subset Table - Table 3 with Unique Values of Column Conc_D and then linked Table 1 with Table 3.  there were no circular Dependencies this time as Conc_D had unique values.

Regards,

Vamshi Regalla 

[v-easonf-msft]

Hi,  @Anonymous 

If the calculated column B of Table1 and the calculated column C of Table2 are created based on the same column A1 in table1, it is not recommended to establish a relationship between column B and column C.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Thank you. is there a workaround to establish relationship between these two tables considering 3 columns( vendor Name, Yeaar and Month) from each table 

[v-easonf-msft]

Hi,  @Anonymous 

Take a try to  convert the calculated column B of Table1 into a actual column, for example, create a custom column B2 in 'Transform Data' to associate the corresponding fields. 

Best Regards,
Community Support Team _ Eason