Forum Discussion
Two Calculated columns with RELATEDTABLE gives Circular Dependency
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?
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.
5 Replies
- uzuntasgokberk
Super User
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" )Kind Regards,
Gรถkberk Uzuntaล
๐ If this post helps, then please consider Accepting it as a solution and giving Kudos โ it helps other members find answers faster!
๐ Stay Connected:
๐ Medium |
๐บ YouTube |
๐ผ LinkedIn |
๐ท Instagram |
๐ฆ X |
๐ฝ Reddit |
๐ Website |
๐ต TikTok | 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.
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 ๐ )
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.
