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.

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

hebe 

could you pls provide some sample data and expected output?

hebe's avatar
hebe
Frequent Visitor
2 years ago

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 🙂 )

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.