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 v...
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.

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.