lookupvalue circular dependency error

[PrzemekC]

Dear Power BI Community,

I'm trying to lookup value from [table 2] to my main [table 1]. I have 4 keys, I need to check which of them are listed in [table 2]. It looks like that:

[table 1]

column 1	column 2	column 3	column 4	1st key	2nd key	3rd key	4th key
1	11	a	aa	1-a	11-a	1-aa	11-aa
2	22	b	bb	2-b	22-b	2-bb	22-bb
3	33	c	cc	3-c	33-c	3-cc	33-cc
4	44	d	dd	4-d	44-d	4-dd	44-dd
5	55	e	ee	5-e	55-e	5-ee	55-ee

[table 2]

key

1-a

22-b

3-cc

44-dd

5-e

I tried to use Lookupvalue formula, but it returned circular dependency error in 2nd formula. Then, I tried to use RELATED function, but I didn't support combination with USERELATIONSHIP.

Do you have any idea how I could tackle this?

[DoHoang]

I got the same error and could solved by check relationship and inactive wrong relationships. From my end, I checked all relationships related to table 2 and found 1 relationship was wrong. I delete the relationship then click enter LOOKUP formula again. Then it worked.

[Anonymous]

If you are able to pull the column from table2 to table1 using RELATED function and then that would be easy to comapre row by row and create a calculated column... 

[PrzemekC]

Yes, but I'm only able to do it using active relation. Table 2 contains only those keys, and I need to know which of them are listed in all 4 keys from Table 1. Comparing row by row is not an option, as there is too much data, and it has to be done automatically.

In Excel I would just create another 4 columns, with vlookup function to each key, and then just merge everything to one column. But in DAX I can't do it as I'm receiving circular dependency error...

EDIT:

Ok, I found one solution, but it's not really sophisticated, and I'm not sure how it will handle large data model. On top of that, I prefer to know if two keys from one row were listed in Table 2. Does anyone have idea how to handle it in easier way?

result = IF(LOOKUPVALUE('Table 2'[key];'Table 2'[key];'Table 1'[1st key])<>"";LOOKUPVALUE('Table 2'[key];'Table 2'[key];'Table 1'[1st key]);
    IF(LOOKUPVALUE('Table 2'[key];'Table 2'[key];'Table 1'[2nd key])<>"";LOOKUPVALUE('Table 2'[key];'Table 2'[key];'Table 1'[2nd key]);
        IF(LOOKUPVALUE('Table 2'[key];'Table 2'[key];'Table 1'[3rd key])<>"";LOOKUPVALUE('Table 2'[key];'Table 2'[key];'Table 1'[3rd key]);
            IF(LOOKUPVALUE('Table 2'[key];'Table 2'[key];'Table 1'[4th key])<>"";LOOKUPVALUE('Table 2'[key];'Table 2'[key];'Table 1'[4th key]);""))))

EDIT 2:

Unfortunately, above solution also didn't work for me. This result has to be linked with [Table 2], creating relation based on this calculated column returns with an error. Anyone has any idea?

[BharatRathod]

@PrzemekC

Hi, I had the same error, I was trying to do a lookup search on a column which is result of previous lookup function hence this was showing circular error. to resolve this i followed following steps!

1) Created duplicate table of a reference table

2) Created a relationship between master data and duplicate table under relationship window

3) Lookup function referring resulting column from duplicate table

Try these steps! they worked perfectly for me, hope this might work for you as well.

[BharatRathod]

@PrzemekC

Hi, I was getting this error because my lookup reference was part of pervious lookup result (I hope I am not adding confusion). This error can be easliy resolved by Duplicating reference table and build new relationship. once relationships are eshtablished you can use lookup function to refer resultcolumn from duplicate table this will not create circular error.

Try this! this worked for me perfectly, hope this will work for you as well..!!