Circular dependency in a calculated column.

[analystict]

Hey there,

I have a table called Invoices which looks like this:

AccountID | Date        | InvoiceID | Amount | ProductID | InvoiceNum   | Product
1         |  02/12/01   | 153452C   | 500    | 8         | 2            | Package 1
1         |  02/25/01   | 153452F   | 2500   | 9         | 3            | Package 2
1         |  02/26/01   | 153453F   | 2500   | 9         | 2            | Package 2
1         |  02/05/01   | 15D212C   | 500    | 8         | 1            | Package 1
5         |  02/24/01   | 15X112C   | 1000   | 9         | 1            | Package 2

InvoiceNum is a calculated column which calculates the true index of the invoice according to the invoice's date. Here is the measure for reference:

InvoiceNum = 
RANKX(
    FILTER(
        Invoices,Invoices[AccountID] = EARLIER(Invoices[AccountID])
    ),
    Invoices[Date],,ASC
) 

Product is a calculated column which takes the product's name according to the invoice's ProductID, from a table called Products:

id | name
8  | Package 1
9  | Package 2

By this formula:

Product = 
CALCULATE(
    MINX (
        Products,
        [name]
    ),
    USERELATIONSHIP(Invoices[ProductID], Products[id])
)

What I am trying to do is exactly as I did in InvoiceNum, however - I want it to count the occurrence of invoices that their Product is only Product 2 for instance. Let's call this measure InvoiceNumPackage2 for intsnace.

EXAMPLE:

Invoice 153452F would be the 1st occurence (for AccountID 1).

Invoice 153453F would be the 2nd occurence (for AccountID 1).

Invoice 15X112C would be the 1st occurence (for AccountID 5).

What I tried is this:

InvoiceNumPackage2 = 
RANKX(
    FILTER(
        Invoices,Invoices[AccountID] = EARLIER(Invoices[AccountID]) &&
        Invoices[Product] = "Online Immigration System - Web Application"
    ),
    Invoices[Date],,ASC
) 

However, I'm getting a circular dependency error.

I'm not sure how to approach this, would love some assistance!

Thanks.

[Nolock · Accepted solution]

Hi,

please could you post the error message? I've tried to build the same 2 tables and new computed columns like you have but I don't have any errors.

The only thing I did additionally was adding another column InvoiceNumPackage3 which shows the Rank of InvoiceNumPackage2 only on rows which contains the Product 9.

InvoiceNumPackage3 = 
IF(
    Invoices[Product] = "Product 9"; 
    Invoices[InvoiceNumPackage2]; 
    BLANK()
)

[analystict]

Thanks!

However, the error was different. After looking into it slightly more I found an active relationship between the invoices table and another table which probably caused the circular dependency, now everything seems in order.

[v-chuncz-msft]

@analystict ,

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.