cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
kjkjkjnk
Helper I
Helper I

Can't Solve Circular Dependency, CALCULATE Can't See Values

Hello!

I previously had a problem of replacing a value from a table three dependencies deep.  Below is the link and the solution I found. 

I recently got another request for the same issue but to pull a different value.  Figured it wasn't a problem and just made a new column with the same code, replacing "Table4[Table 4 "account"]" with "Table4[Table 4 "new_request"]" (code below).  

This caused the circular dependency error.  From my research, the solution is to add CALCULATE with a REMOVEFILTERS filter.  However, the code for some reason gives a "Cannot Find Name" error for all column names for table1, which is where the new column sits (third example below).  

 

Moving the logic to a different table isn't a viable solution, and isn't sustainable anyway if these requests keep coming in.  

Any help would be greatly appreciated, thank you! 

 

(Original, no issues) 

Replace =
IF('Table1'[Table 1 "names"] = "",
    (
        VAR Test1 = LOOKUPVALUE(Table2[Location 1 keys]Table2[Nameskey]Table1[Names keys])
        VAR Test2 = LOOKUPVALUE(Table3[Location 2 keys]Table3[Location 1 keys]Test1)
        VAR Test3 = LOOKUPVALUE(Table4[Table 4 "account"]Table4[Location 2 keys]Test2)
        Return Test3
    ),
    Table1[Table 1 "names"]
)



(New code, causes circular dependency error) 

ReplaceNEW =
IF('Table1'[Table 1 "names"] = "",
    (
        VAR Test1 = LOOKUPVALUE(Table2[Location 1 keys]Table2[Nameskey]Table1[Names keys])
        VAR Test2 = LOOKUPVALUE(Table3[Location 2 keys]Table3[Location 1 keys]Test1)
        VAR Test3 = LOOKUPVALUE(Table4[Table 4 "new_request"]Table4[Location 2 keys]Test2)
        Return Test3
    ),
    Table1[Table 1 "names"]
)



(Attempted Solution, all Table1 columns give a "Cannot Find Name" error)

ReplaceNEW =
CALCULATE
(
IF('Table1'[Table 1 "names"]*ERROR* = "",
    (
        VAR Test1 = LOOKUPVALUE(Table2[Location 1 keys]Table2[Nameskey]Table1[Names keys]*ERROR*)
        VAR Test2 = LOOKUPVALUE(Table3[Location 2 keys]Table3[Location 1 keys]Test1)
        VAR Test3 = LOOKUPVALUE(Table4[Table 4 "new_request"]Table4[Location 2 keys]Test2)
        Return Test3
    ),
    Table1[Table 1 "names"]*ERROR*
),
REMOVEFILTERS(Table 1[Replace])
)




(Original issue) 
Solved: Re: Replace a value with data from a different tab... - Microsoft Fabric Community

2 REPLIES 2
Syk
Super User
Super User

Can you provide a screenshot of what your data model looks like and the tables you have? I think you just need to rework your model to avoid these confusing measures.

Here's the table layout: 
Table Structure: 
Table 1 (if blank) --> Table 2 --> Table 3 --> Table 4 (use this value) 

Table 1 -> Table 2: Many to Many, Cross Filter Direction: Both
Table 2 -> Table 3: Many to One, Cross Filter Direction: Both 
Table 3 -> Table 4: Many to One, Cross Filter Direction: Both 

The data itself is imported from Dynamics 365 so I can't rework the model unfortunately.  

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors