Calculating between two tables - circular dependency?

[jwin2424]

Hello, 

I have three tables as follows:

Table 1 and Table 3 are joined together with a Many to one reltaionship with the many side being on table 3 and the one side beind on table 1. The cross filter direction with this relationship is 'Both.' They are joined together by the OPP ID.

Table 2 is not joined in any way. It is simply a reference table with some % data on it. 

Table 1 is the only table with a stage and a quarter number, so I concated the two together and used it as my primary lookup key. I did a lookup from Table 2 to return to % to win. The value of each OPP ID is on Table 3. I did a lookup with the OPP ID to return the concat value. I used the concat value to lookup from table Table and return the %. 

I have tried this a few ways, and I keep getting a circular dependency error. 

I have tried getting the % to win two different ways. I cant seem to get the % either through the diconnected table nor the connected one. Can anyone help?

I am trying to get the line items on Table 3 to the value and multiply it by the % to win based on the concat number. 

Thanks!

[v-yanimei-msft · Accepted solution]

Hi all , 

@QuentinBl , thanks for your concern about this case, I have some additions for problem solving.

@jwin2424 , thank you for giving a specific description of the problem. Based on your description, I may not be able to understand and solve your problem very well. Depending on the content of your text, I will give you the following steps to solve the problem.

1. Create three tables as the picture you show.

The original Table 1.

The original Table 2.

The original Table 3.

In Table1, New column % to win and input:

% to win = LOOKUPVALUE(Table2[% to win],Table2[Concat],Table1[Concat])

In Table 3, New column Concat and input:

Concat = LOOKUPVALUE(Table1[Concat],Table1[OPP ID],Table3[OPP ID])

2. Manage relationships as the text “Table 1 and Table 3 are joined together with a Many to one reltaionship with the many side being on table 3 and the one side beind on table 1. The cross filter direction with this relationship is 'Both.' They are joined together by the OPP ID. Table 2 is not joined in any way. It is simply a reference table with some % data on it.".

3. In table 1, New column primary lookup key as the text “Table 1 is the only table with a stage and a quarter number, so I concated the two together and used it as my primary lookup key.” and input:

primary lookup key = CONCATENATE(Table1[Stage],Table1[Close Quarter])

4. The operation as the text “I did a lookup from Table 2 to return to % to win. The value of each OPP ID is on Table 3. I did a lookup with the OPP ID to return the concat value. I used the concat value to lookup from table Table and return the %.” has been done in step1.

5. In Table 3, New column % to win value as the text “ I am trying to get the line items on Table 3 to the value and multiply it by the % to win based on the concat number.” and input:

% to win value = Table3[Estimated Value]*RELATED(Table1[% to win])

You can modify the above DAX codes according to your needs.

I have no way of analyzing what you are causing the loop error because I don’t know the data what is.

To better solve your problems, you may need to answer some questions.

1. What is the data you have used in the Measure Test as your picture? Please give the data of table Opportunity ,Opportunity Product, Forecast Statistics with pictures.

2. What is your desired outcome? Please give your specific requirements and an example diagram of what to expect.

Best Regards,

Caroline Mei

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jwin2424]

This did work. I am hoping you might be able to elaborate a little on why RELATED was necessary for this? I am not sure where the circular dependecy came from. I was able to get the % into table 1, so I am not sure why I couldn't also just lookup the value on pull it into table 3 as well. It doesn't appear as if there is any circular dependency. 

In any case, thank you! It is working now. 

[QuentinBl]

Hello, 

Can you not do it in Power query ? If the table 2 only purpose is to give a win % to table 1. 

[jwin2424]

I tried this and got the same ciclical reference error

= Table.AddColumn(#"Filtered Rows", "Estimated Close Quarter 2", each Table.First(
Table.SelectRows(
Calendar,
each [fiscal_calendardate] = Opportunity[estimatedclosedate]
)
)[fiscal_quarter])

[jwin2424]

Well, I dont know how to do lookupvalue in power query. I need to lookup the concat value and both the close quarter and stage are calculated columns from the report dashboard. I dont know how to do that in power query. 

Here is an example. How do I do this in power query?

Estimated Close Quarter = LOOKUPVALUE('Calendar'[fiscal_quarter], 'Calendar'[fiscal_calendardate].[Date], Opportunity[estimatedclosedate].[Date])