Forum Discussion

hebe's avatar
hebe
Frequent Visitor
2 years ago
Solved

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 st...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
hebe
Frequent Visitor
2 years ago

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.