Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamically Filter a visual based on Max value of another visual

I have been trying to dynamically filter a column chart and a matrix based on the max value of another visual.  I am using the following measure for a card that gives me the sum of the max downtime and dynamically filters for the card value:

 

 

Duration max per Downtime = 
MAXX(
	KEEPFILTERS(VALUES('DowntimeL1'[DTReason])),
	CALCULATE(SUM('DowntimeL1'[Duration]))
)

 

 

 However, I want to be able to create a column chart and a matrix visual that shows the stations that had downtime for the Max DT reason of the card visual, is this possible? The left side shows the equipment downtime by reason, the top reason had a total of 79 minutes of Downtime for the week (Relative date filter - last 7 days).  The right side shows the equipment dowtime for the top reason but I have been manually filtering the column chart and the matrix visual and would like these to update dynamically as the data on the left side changes

 

Any guidance you can provide would be appreciated.  Thank you.

Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi Anonymous,

     

    Sorry for the late reply.

    I modify the demo, maybe it works.

    Here is the output:

     

    Best Regards,

    Link

15 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I am fairly new to PowerBi and I am struggling to get this to work... I have been reading through all of the forums and have not been able to find the desired solution.  I found this post that I thought might be the solution:  https://community.powerbi.com/t5/Desktop/Top-N-products-sold-per-product-category/m-p/270082 but I kept getting a circular dependency error on the RANKX calculated column that I could not resolve.  Does anyone else have any more advice for me?  Basically, if the top Downtime reason in the two visuals on the left is "mechanical" (for example), then I want the two visuals on the right to filter for all the stations that had a "mechanical" Downtime reason.

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous,

     

    Try measure as:

    Measure1 =

VAR _table =

    SUMMARIZE ( 'Table', 'Table'[Downtime], "value", SUM ( 'Table'[Total] ) )

VAR _max =

    MAXX ( _table, [value] )

RETURN

CALCULATE ( MAX ( 'Table'[Downtime] ), FILTER ( _table, [value] = _max ) )
    Measure2 =

var measure1= CALCULATE([Measure1],ALL('Table'))

return IF(SELECTEDVALUE('Table'[Downtime])=measure1,1,0)

    Here is the output:

     

    Here is the demo, please try it: Dynamically Filter a visual based on Max value of another visual

     

    Best Regards

    Link

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for the follow-up. I have tried your solution and unfortunately, it does not work in my file. Instead, it blanks out the Chart and Matrix revealing no records. Below are my measures:

      Measure1 = 
var _table= SUMMARIZE('DowntimeL1','DowntimeL1'[Downtime],"value",SUM('DowntimeL1'[Duration]))
var _max=MAXX(_table,[value])
Return
CALCULATE(
    MAX('DowntimeL1'[Downtime]),
FILTER(_table,
 [value]=_max )
)


Measure2 = 
var Measure1= CALCULATE([Measure1],ALL('DowntimeL1'))
return IF(SELECTEDVALUE('DowntimeL1'[Downtime])=Measure1,1,0)

       

       Am I missing something? Did I intrepret something incorrectly? Please advise. Thank you. 

      • v-xulin-mstf's avatar
        v-xulin-mstf
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous,

         

        Could you provide your pbix? After handling sensitive information.

         

        Best Regards,

        Link