Circular Dependency using Selectcolumns

[MarioCadena]

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?

[v-kkf-msft]

Hi @MarioCadena ,

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.

[MarioCadena]

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]

Hi @MarioCadena ,

Does your problem have been solved?

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

[v-kkf-msft]

Hi @MarioCadena ,

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.

[amitchandak]

@MarioCadena , 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,<> ,<> )))