Circular dependency was detected issue

[Anonymous]

Hi All,

I have a requirement to lookup for a row value of a column (column name is : "IRB Staff/Study Team" and Table name is "Append1") based on rank measure and populate the next row value in a new column .

To achieve this, 

I am creating a rank measure in my report as below using DAX:

1. Create new measure

Rank_Var = RANKX(

ALLSELECTED(Append1),

CALCULATE(SUM(Append1[Index]))

,,ASC

)

Now, I need to use this measure which I created(Rank_Var) for a lookupvalue for which I created a column

1. Create new column

Lookup_Var=

LOOKUPVALUE(Append1[IRB Staff/Study Team], need to use the measure, need to use the measure +1)

But I am not able to use the measure in the calculated column.

Can someone please help me fix this with a workaround?

Thank you

Poojitha

[Sumanth_23]

hi @Anonymous - The circular reference error is occuring since you are using the Append1 table to derive a rank and then also using it in the calculated column. 

You can try moving the rank calculation to Query Editor so it come through as a value into your data model - then you should be able to add the calculated column Look_var in the data model as per your approach below

Please mark the above comment as a solution to help others find it more quickly. Also please provide a 👍 if my comment helped with solving your issue. Thanks!

[Anonymous]

Hi @Sumanth_23

As I am looking for a dynamic index value , I have used this in DAX and not in query editor, is there any other alternative by not having it done from query editor.

Thank you

Poojitha

[v-kelly-msft]

Hi @Anonymous ,

Could you pls tell me more about your senario of why you need to use "lookupvalue " function?

"Lookupvalue"function is used to search values in columns between tables,which is not suitable in your case.If possible ,pls provide some sample data with expected output,I would advise you another available way.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft 

My requirement is not compare values of a column(Col A). if the current value is equal to the next value,then show as zero , else show as one in a new column (Col New).

For this, I created a rank function to dynamically rank based on the slicers I select.

Then now I am trying to use a lookup function to populate the next cell value based on the rank I created into the new column.

Then I will write a logic saying, if Col A=Col New, then 0 else 1.

This is the scenario I am using.