Forum Discussion

SM321's avatar
SM321
Frequent Visitor
3 years ago
Solved

Bidirectional filtering with Clustered Column Chart

I am trying to create a column chart that should show me the total count of rows vs count of empty values for a specific field. And clicking on the bar, should filter the details table and show the values which has empty rows. 

For ex: In the attached PBIX, I am looking to show bars of Product Name, Product Type, Team, Lead, Owner as columns. And let's say Product Type column should show as 17 (Total Count) vs 8 (Count of empty values of Product Type) and when I click on 8, the above table should show only the empty values for product type (Bidirectional filtering) and similarly for others. Any suggestion, how to get this done?

When I filter the category slicer, it refreshes rest of visuals but for the column chart it is not refreshing any other tile. 

 

Thanks in advance!

PowerBI
Testing.pbix

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi SM321 ,

     

    Please try:

    First create a new table:

    For X-Axis = {"PL_","PN_","PO_","PT_","PType_","All"}

    Then apply this measure to the Clustered Column Chart:

    Measure = SWITCH(SELECTEDVALUE('For X-Axis'[Value]),
"PL_",[PL_],
"PN_",[PN_],
"PO_",[PO_],
"PT_",[PT_],
"PType",[PType_],
"All",COUNTROWS(ALL('Table')))

    Output:

    Apply the measure Flag to the table visual's filter:

    Flag = 
SWITCH(TRUE(),
SELECTEDVALUE('For X-Axis'[Value])="PL_"&&SELECTEDVALUE('Table'[Product Lead])=BLANK(),1,
SELECTEDVALUE('For X-Axis'[Value])="PN_"&&SELECTEDVALUE('Table'[Product Name])=BLANK(),1,
SELECTEDVALUE('For X-Axis'[Value])="PO_"&&SELECTEDVALUE('Table'[Product Owner])=BLANK(),1,
SELECTEDVALUE('For X-Axis'[Value])="PT_"&&SELECTEDVALUE('Table'[Product Team])=BLANK(),1,
SELECTEDVALUE('For X-Axis'[Value])="PType_"&&SELECTEDVALUE('Table'[Product Type])=BLANK(),1,
NOT(ISFILTERED('For X-Axis'[Value])),1,
SELECTEDVALUE('For X-Axis'[Value])="All",1)

    Final output:

    Best Regards,

    Jianbo Li

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

3 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi SM321 ,

     

    Please try:

    First create a new table:

    For X-Axis = {"PL_","PN_","PO_","PT_","PType_","All"}

    Then apply this measure to the Clustered Column Chart:

    Measure = SWITCH(SELECTEDVALUE('For X-Axis'[Value]),
"PL_",[PL_],
"PN_",[PN_],
"PO_",[PO_],
"PT_",[PT_],
"PType",[PType_],
"All",COUNTROWS(ALL('Table')))

    Output:

    Apply the measure Flag to the table visual's filter:

    Flag = 
SWITCH(TRUE(),
SELECTEDVALUE('For X-Axis'[Value])="PL_"&&SELECTEDVALUE('Table'[Product Lead])=BLANK(),1,
SELECTEDVALUE('For X-Axis'[Value])="PN_"&&SELECTEDVALUE('Table'[Product Name])=BLANK(),1,
SELECTEDVALUE('For X-Axis'[Value])="PO_"&&SELECTEDVALUE('Table'[Product Owner])=BLANK(),1,
SELECTEDVALUE('For X-Axis'[Value])="PT_"&&SELECTEDVALUE('Table'[Product Team])=BLANK(),1,
SELECTEDVALUE('For X-Axis'[Value])="PType_"&&SELECTEDVALUE('Table'[Product Type])=BLANK(),1,
NOT(ISFILTERED('For X-Axis'[Value])),1,
SELECTEDVALUE('For X-Axis'[Value])="All",1)

    Final output:

    Best Regards,

    Jianbo Li

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

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi SM321 ,

     

    The sample you provided is protected by your org's information protection policy.

    Please provide me with your pbix file after removing sensitive data.

     

    Refer to:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

    How to apply sensitivity labels in Power BI - Power BI | Microsoft Learn

     

    Best Regards,

    Jianbo Li

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