Forum Discussion

jaime_blackwell's avatar
jaime_blackwell
Icon for Helper I rankHelper I
5 years ago
Solved

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

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!

 

Need Help
  • camargos88's avatar
    camargos88
    5 years ago

    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's avatar
    v-lionel-msft
    5 years ago

    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.

7 Replies

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      jaime_blackwell
      Icon for Helper I rankHelper I
      5 years ago

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

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

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      jaime_blackwell
      Icon for Helper I rankHelper I
      5 years ago

      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's avatar
        jaime_blackwell
        Icon for Helper I rankHelper I
        5 years ago

        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?

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.