Unable to reference the source table of a custom column without triggering a cyclic reference error?

[jaime_blackwell]

Hi Community,

I've written a custom column in a table of data. The table is called 'Recordable Hours' and contains 'Client Name' as well as values for 'Decimal Time'. 

I would like the resulting custom colum named 'Total_Client_Time_for_the_Week' to show the total sum of 'Decimal Time' for all rows with the same 'Client Name'. So the outcome is that if the row has the same 'Client Name' it should also have the same value in 'Total_Client_Time_for_the_Week'.

The expression I've used is below.

Total_Client_Time_for_the_Week = 

let
client1 = "Recordable Hours"[Client Name] ,

rows = Table.SelectRows( #"Recordable Hours", [Client Name] = client1 ),

result = List.Sum(Table.ToList(rows[Decimal Time]))

in
result

I am getting Expression.Error: A cyclic reference was encountered during evaluation. 

Could you please assist me on how to achieve the intended values and get around this error?

Thank you!

[camargos88 · Accepted solution]

@jaime_blackwell ,

You can create a custom column with something like:

let _name = [Client Name] in
 List.Sum(Table.SelectRows(#"Recordable Hours", each [Client Name] = _name)[Decimal Time])

Be sure that #"Recordable Hours" is the last step before the new column.

[v-lionel-msft · Accepted solution]

Hi @jaime_blackwell ,

Or create a calculated column.

Total_Client_Time_for_the_Week = 
CALCULATE(
    SUM('Recordable Hours'[Decimal Time]),
    ALLEXCEPT( 'Recordable Hours', 'Recordable Hours'[Client Name] )
)

Best regards,
Lionel Chen

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

[camargos88]

@jaime_blackwell ,

How are you using this query ?

If you call this query inside the same base table it will throw an error like that.

Can you show how you are using it ? Also, if possible share the pbix (masking any sensitive data).

[jaime_blackwell]

Hi, Thanks for your response. Yes I am trying to add this custom column into the base table. I need to see the result value for every row of this table. This will mean the same value may appear multiple times. Is there a way to achieve this without getting the error? (This is required for future calculations applied to each row individually.)

I don't think I can provide the PBX as removing all sensitive data would require removing all of the data sorry!

[jaime_blackwell]

To further clarify, I need to know how to reference the current row when creating a custom column. For example, if I want to create a table containing all records that match 'Client Name' for the current row, how do I refer to the current row without getting the cyclic reference error?

[camargos88 · Accepted solution]

@jaime_blackwell ,

You can create a custom column with something like:

let _name = [Client Name] in
 List.Sum(Table.SelectRows(#"Recordable Hours", each [Client Name] = _name)[Decimal Time])

Be sure that #"Recordable Hours" is the last step before the new column.

[camargos88]

@jaime_blackwell ,

Try this one:

Total_Client_Time_for_the_Week = 

let
client1 = "Recordable Hours"[Client Name] ,

rows = Table.SelectRows( #"Recordable Hours", each [Client Name] = client1 ),

result = List.Sum(rows[Decimal Time])

in
result

[jaime_blackwell]

Thank you for your suggestion. Unfortunately this still returns the error:

Expression.Error: A cyclic reference was encountered during evaluation.