cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PrzemekC Frequent Visitor
Frequent Visitor

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?

 

 

4 REPLIES 4

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

PrzemekC Frequent Visitor
Frequent Visitor

Re: lookupvalue circular dependency error

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 Frequent Visitor
Frequent Visitor

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

Highlighted
BharatRathod Frequent Visitor
Frequent Visitor

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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 285 members 3,167 guests
Please welcome our newest community members: