Two Calculated columns with RELATEDTABLE gives Circular Dependency

[hebe]

I need to find all persons that have a certain value in another table but not another value.

For instance a person that has a valid licence and also a cancellation of that licence and they are stored in a license-actions table.

I tried creating these two Calculated Columns: 

Recall = 
VAR _SEL = SELECTCOLUMNS ( RELATEDTABLE('LicenseStatus'), "ID", [Status] )
RETURN
IF ( "RECALL" IN _SEL, "YES", "NO" )

License = 
VAR _SEL = SELECTCOLUMNS ( RELATEDTABLE('LicenseStatus'), "ID", [Status] )
RETURN
IF ( "LICENSE" IN _SEL, "YES", "NO" )

to be able to ask this question:

HaveCancelledLicense =
IF ( License = "YES" and Cancelled = "YES", "YES", "NO" )

but I get "Circular dependecy error" when I create the second Calculated Column. Why? They dont reference each other?

[ryan_mayu]

@hebe 

could you pls provide some sample data and expected output?

[hebe]

I created a sample file to display the problem:

https://1drv.ms/u/c/9f00986c82fa1657/ES1QHM3WKKFMuq8kc5DWiwUB4BOZIRGqEfLSWLAOfobaBA?e=psTQYg 

The Calcluated Columns: "Issued" and "Cancelled" in the Person table are commented out since they create a Circular Reference if they are uncommented. Why am I not allowed to have more than one Calculate Column with RELATEDTABLE in the same table?

(Yes I know this is not the optimal solution, but this is how the source database looks like and I am aware that there are other (better) ways to solve this, but I want to know why this solution doesn't work 🙂 )

[v-denglli-msft · Accepted solution]

Hi @hebe ,

You can try the following DAX to add calculated columns.

Issued = 
VAR _SEL= CALCULATE (
        COUNTROWS ( 'Status' ),
        ALLEXCEPT(Person,Person[Id]),
        'Status'[Status] = "Issued"
    )
RETURN IF ( _SEL > 0, "Yes", "No" )

Cancelled = 
VAR _SEL= CALCULATE (
        COUNTROWS ( 'Status' ),
        ALLEXCEPT(Person,Person[Id]),
        'Status'[Status] = "Cancelled"
    )
RETURN IF ( _SEL > 0, "Yes", "No" )

Please see the attached pbix for reference.

Best Regards,

Dengliang Li

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

[hebe]

Thankyou for your reply uzuntasgokberk. I dont think the answer in the post you are referring to is relevant in this case. They have two measures that are obviously dependent on the result of each other and hence creates a circular dependency. In my case there is no dependency between the calculated columns so I dont understand why I get the circular reference error message.

This is all DAX. I made an error when I wrote the code for the "HaveCancelledLicense" so you are correct that it should be "&&" and not "and".

The problem seems to be that I use "RELATEDTABLE" on the same table in two separate calculated columns. I dont understand why that would not be allowed? (If that is the problem.) It is two completely separate columns and I dont see why they would effect each other but each of them work by themself but not together.

[uzuntasgokberk]

Hello @hebe,

Circular dependecy error problem was shared before in the community. You can add column in PowerQuery.

https://community.fabric.microsoft.com/t5/Desktop/Circular-dependency/m-p/2978637#M1017963

And I don't understand the code have you create this code in PQ or DAX(new column). Because in DAX and function can't be use like this:

HaveCancelledLicense =
IF ( License = "YES" and Cancelled = "YES", "YES", "NO" )

The correct way is:

HaveCancelledLicense =
IF ( License = "YES" && Cancelled = "YES", "YES", "NO" )

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

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