How to filter the table by chart visual?

[PreacherBaby]

Hey there!

So what we have here are:

1) "Status" chart

2) "Readiness" column in the table

Both status chart sections and flag icons in the column are measures. A specific table is created for the chart, without any relationship to the other tables. 

What I want to:

Whenever a user clicks on a section (say red, for example), the column in the table should display only the projects with red flag icons:

For now, whenever I click on whatever section of the chart - the column doesn't get filtered.

Like here:

RESOURCES IF NEEDED

Chart table:

1) table itself

2) color conditions

Color = 
SWITCH('Status Chart Filter Table'[Flag],

"FlagHigh", "At Plan",

"FlagMedium", "Behind Plan",

"FlagLow","At Risk",

"FlagBlack", "Other"
)

3) table expression

Status Chart Filter Table = 

VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 1, 4, 1 ), "Sort", [Value] )

VAR T2 =

    ADDCOLUMNS (

        T1,

        "Flag", SWITCH ( [Sort], 1, "FlagHigh", 2, "FlagMedium", 3, "FlagLow", 4, "FlagBlack" )

    )

RETURN

    T2

4) count the number of a particular color:

Flag Count = 

VAR CurrentFlag =SELECTEDVALUE('Status Chart Filter Table'[Flag] )
VAR T1 = ADDCOLUMNS ( VALUES ( 'All Project v2'[Project Name] ), "@Flag", [stat chart] )
VAR T2 = FILTER ( T1, [@Flag] = CurrentFlag )

RETURN COUNTROWS ( T2 )

Conditional formatting expression used to mark "Readiness columns" with respective flags (if needed):

_Overall_Status_Flag = 

VAR Unit_Readiness = [Upcoming Unit Readiness] #showing values in percentages 
VAR count_delimiter =
    LEN ( Unit_Readiness ) - LEN ( SUBSTITUTE ( Unit_Readiness, ",", "" ) ) 
#in the case when there are two values delimited with ",". Because conditional formatting #works on numbers only, we have to split the string (e.g. 100%, 99%) and check if they meet #conditions and assign a flag to them
VAR last_Unit_Readiness_position =
    IF (
        count_delimiter = 0,
        BLANK (),
        FIND ( "@", SUBSTITUTE ( Unit_Readiness, ",", "@", count_delimiter ) )
    )
VAR last_Unit_Readiness_len =
    IF (
        count_delimiter = 0,
        BLANK (),
        LEN ( Unit_Readiness ) - last_Unit_Readiness_position
    )
VAR last_Unit_Readiness =
    VALUE (
        SWITCH (
            TRUE (),
            ISERROR ( SEARCH ( "%", Unit_Readiness ) ), BLANK (),
            count_delimiter = 0, LEFT ( Unit_Readiness, LEN ( Unit_Readiness ) - 1 ),
            LEFT (
                RIGHT ( Unit_Readiness, last_Unit_Readiness_len ),
                last_Unit_Readiness_len - 1
            )
        )
    ) / 100
VAR upcoming_gates = [Upcoming Gate] # the [Unit Readiness] shows the readiness of the #particular gate
VAR count_delimiter_gate =
    LEN ( upcoming_gates ) - LEN ( SUBSTITUTE ( upcoming_gates, ",", "" ) )
VAR last_gate_position =
    IF (
        count_delimiter_gate = 0,
        BLANK (),
        FIND ( "@", SUBSTITUTE ( upcoming_gates, ",", "@", count_delimiter_gate ) )
    )
VAR last_gate_len =
    IF (
        count_delimiter_gate = 0,
        BLANK (),
        LEN ( upcoming_gates ) - last_gate_position
    )
VAR last_gate =
    SWITCH (
        TRUE (),
        count_delimiter_gate = 0, upcoming_gates,
        LEFT ( RIGHT ( upcoming_gates, last_gate_len ), last_gate_len )
    )


VAR min_p_calc =         
    CALCULATE (
            SELECTEDVALUE ( 'Unit Readiness Table'[Y_min] ), #[Y_min] is a min. the threshold value for readiness not to be red 
            'Unit Readiness Table'[P Gate] = TRIM ( last_gate ) # [P Gate] is a gate's name
        )

VAR min_m_calc = 
    CALCULATE (
            SELECTEDVALUE ( 'Unit Readiness Table M'[Y_min] ),
            'Unit Readiness Table M'[M Gate] = TRIM ( last_gate )
        )
VAR y_min_P =
    IF (
        SELECTEDVALUE ( 'All Project v2'[Project Type] ) = "PMP",
        min_p_calc,
        min_m_calc
    )

VAR max_p_calc = 
    CALCULATE (
            SELECTEDVALUE ( 'Unit Readiness Table'[Y_max] ),# Y_max is the threshold to be green color
            'Unit Readiness Table'[P Gate] = TRIM ( last_gate )
        )
VAR max_m_calc = 
    CALCULATE (
            SELECTEDVALUE ( 'Unit Readiness Table M'[Y_max] ),
            'Unit Readiness Table M'[M Gate] = TRIM ( last_gate )
        )

VAR y_max_P =
    IF (
        SELECTEDVALUE ( 'All Project v2'[Project Type] ) = "P",
        max_p_calc,
        max_m_calc
    )
VAR overall_status =
    SWITCH (
        TRUE (),
        OR (
            TRIM ( last_gate )
                IN {
                "Start In",
                "Start De",
                "Start Proto",
                "Insufficient Data",
                "No Next Gate"
            },
            ISBLANK ( last_Unit_Readiness )
        ), BLANK (),
        last_Unit_Readiness < y_min_P, "FlagLow",
        last_Unit_Readiness > y_max_P, "FlagHigh",
        "FlagMedium"
    )
RETURN
    overall_status

[v-mengzhu-msft]

Hi @PreacherBaby ,

If you can't filter one visual by the other, perhaps you need to consider whether the relationship between the two tables is established.

Best regards,

Community Support Team Selina zhu

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

[PreacherBaby]

You are right - there is no relationship, donut chart has its own table with no relationships whatsoever, but there is no way I can establish at least one - It doesn't have anything to make those relationships with!

[v-mengzhu-msft · Accepted solution]

Hi @PreacherBaby ,

Don't you have a common linked column between these two tables? Like some product serial number, or id number or something like that? Maybe you can bridge the two tables that are not related by using a third table that is related.
Because I think you can't do effective filtering if there is no association between the two tables.

Best regards,

Community Support Team Selina zhu

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

[amitchandak]

@PreacherBaby , Based on what I got, You need to change interaction from table to pie as filter https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

[PreacherBaby]

That was a good idea, unfortunately, it didn't work:( 

Let me share more so it might help:
- The legend of the donut chart comes from the calculated table:

- Values for the donut chart come from the measure:

Flag Count = 
VAR CurrentFlag =
    SELECTEDVALUE ( 'Status Chart Filter Table'[Flag] )
VAR T1 =
    ADDCOLUMNS (
        VALUES ( 'All Project v2'[Project Name] ),
        "@Flag", [FRU stat chart] #this measure is a set of conditions, which assigned a 
                                  #corresponding flag to the value in the column 
                                  #that should get filtered
    )
VAR T2 =
    FILTER ( T1, [@Flag] = CurrentFlag )
RETURN
    COUNTROWS ( T2 )

I know there are no relationships between the donut chart table and everything else, but if they play a crucial role here, how would I establish them?