A measure to filter both the chart and column in the table based on conditional formatting of column

[Anonymous]

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.

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-chenwuz-msft]

Hi @Anonymous ,

A little confused, is this you want?

If not, please provide the output you want.

Best Regards

Community Support Team _ chenwu zhu

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

[Anonymous]

So... Is there a way to do this?...

[Anonymous]

Absolutely! You see, whenever I am clicking on a donut chart's section, it filters the table, but not to the degree I want

Your example is what I want, yes

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Sorry for the late reply. You can change the visualization of the table to disable interaction with the chart.

https://learn.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions?tabs=powerbi-desktop#enable-the-visual-interaction-controls

Best Regards

Community Support Team _ chenwu zhu

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