Reply
Highlighted
Frequent Visitor
Posts: 3
Registered: ‎11-14-2017

lookupvalue circular dependency error

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 1column 2column 3column 41st key2nd key3rd key4th key
111aaa1-a11-a1-aa11-aa
222bbb2-b22-b2-bb22-bb
333ccc3-c33-c3-cc33-cc
444ddd4-d44-d4-dd44-dd
555eee5-e55-e5-ee55-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?

 

 

Member
Posts: 94
Registered: ‎05-22-2017

Re: lookupvalue circular dependency error

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... 

Frequent Visitor
Posts: 3
Registered: ‎11-14-2017

Re: lookupvalue circular dependency error

[ Edited ]

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?

Frequent Visitor
Posts: 2
Registered: ‎10-08-2018

Re: lookupvalue circular dependency error

@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..!!

Frequent Visitor
Posts: 2
Registered: ‎10-08-2018

Re: lookupvalue circular dependency error

@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.