Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Circular Dependency using Selectcolumns

Good morning community,

 

just wondering if you could help me with the following.

 

I have created a few calculated columns and the value is used as conditional formatting as per below.

 

 

I was asked to create a piechart or any kind of visual counting the number of highlighted cases for each stage.
Some rows can contain multiple.

I created a table using 
UNION and SELECTCOLUMNS to merge the difference calculated columns and the # of cases, the table looks good but when trying to connect to the main table to be able to filter it when selecting; it shows a circular dependency, I guess due to there are multiple values for the same row.

 

Is there any way to make it work?

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , When I do union ,

    example

    distinct(union(all(Table1[A]), All(TableB[A])))

     

    In this case, if all values in column A of Table b is Unique then that will give an error when one create a relation between in new table and Table B A

     

    Two avoid this

    distinct(union(all(Table1[A]), distinct(TableB[A])))

     

    so in you case , it might be like

    distinct(union(Table1, distinct(selectcolumns(TableB,<> ,<> )))

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    I create measure:

    Count = 
SWITCH( 
    SELECTEDVALUE('Calculated Table'[Stage]), 
    "Big 5", COUNT('Calculated Table'[Type]),
    "Transport Required", 
        CALCULATE(
            COUNT('Calculated Table'[Type]),
            'Calculated Table'[Type] = "Yes" 
        ) - CALCULATE(
                COUNT('Calculated Table'[Type]),
                FILTER(
                    ALL('Calculated Table'),
                    'Calculated Table'[Stage] = "Big 5"
                    && 'Calculated Table'[Type] = "Yes"
                )
            )
    )

     

     

    Is this the output you expect? Could you please share your formulas for calculated columns and calculated tables?

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for your reply.

       

      The solution you provided is my current situation.

      What I need is to be able to filter the table by selecting values from the piechart.

       

      Regards,

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        5 years ago

        Hi Anonymous ,

         

        Try the following formula:

        Filted Row = 
var BigStage = 
    CALCULATETABLE(
        VALUES('Table'[Big 5]),
        'Table'[Big 5] = "Yes"
    )
var TraStage = 
    CALCULATETABLE(
        VALUES('Table'[Big 5]),
        'Table'[Big 5] = "No",
        'Table'[Transport Required] = "Yes"
    )
return 
    IF(
        SELECTEDVALUE('Calculated Table'[Stage]) = "Big 5",
        COUNTROWS( INTERSECT( BigStage, VALUES('Table'[Big 5]) ) ),
        COUNTROWS( INTERSECT( TraStage, VALUES('Table'[Big 5]) ) )
    )

         And put the measure into Big 5 field:

         

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz

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