Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

How to filter the table by chart visual?

Hey there!

So what we have here are:

1) "Status" chart

PreacherBaby_0-1663751492399.png

2) "Readiness" column in the table

PreacherBaby_2-1663751583303.png

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:

PreacherBaby_3-1663751788205.png

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

Like here:

PreacherBaby_0-1664285911025.gif

 

 

RESOURCES IF NEEDED

Chart table:

1) table itself

PreacherBaby_4-1663752472020.png

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

 

 

  

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

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

View solution in original post

5 REPLIES 5
v-mengzhu-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

Anonymous
Not applicable

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!

Hi @Anonymous ,

 

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
Super User
Super User

@Anonymous , 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

Anonymous
Not applicable

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:

PreacherBaby_0-1664353703212.png

- 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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.