Forum Discussion

tomrollinson's avatar
tomrollinson
Frequent Visitor
2 years ago
Solved

Filtering Values displayed in Bar Chart based on a range of ranks

Hi,   I am relatively new to Power BI so would appreciate any help with the below.   I am wanting to be able to limit the number of values returned in a bar chart depending on the rank of a selec...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi tomrollinson ,

     

    First, create an unjoined table and drag the League Position column of this table into the slicer:

    Table 2 = DISTINCT('Table'[League Position])

     

    Create three measures:

    Total Points = SUM('Table'[Points])

Rank = RANKX(ALL('Table'[League Position]),[Total Points],,DESC,Dense)

Measure = 
VAR __selected_value = SELECTEDVALUE('Table 2'[League Position])
VAR __ranking = CALCULATE([Rank],'Table'[League Position]=__selected_value)
VAR __bef = __ranking - 2
VAR __aft = __ranking + 2
VAR _cur_ranking = [Rank]
VAR _result = IF(ISBLANK(__selected_value)|| (_cur_ranking>=__bef && _cur_ranking<=__aft),1)
RETURN
_result

     

    Finally, put the measure in a visual object-level Filter to filter the data to be displayed only if the measure is 1:

     

    The page looks like this:

     

    If you have any other questions please feel free to contact me.

     

    The pbix file is attached.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi tomrollinson ,

 

First, create an unjoined table and drag the League Position column of this table into the slicer:

Table 2 = DISTINCT('Table'[League Position])

 

Create three measures:

Total Points = SUM('Table'[Points])

Rank = RANKX(ALL('Table'[League Position]),[Total Points],,DESC,Dense)

Measure = 
VAR __selected_value = SELECTEDVALUE('Table 2'[League Position])
VAR __ranking = CALCULATE([Rank],'Table'[League Position]=__selected_value)
VAR __bef = __ranking - 2
VAR __aft = __ranking + 2
VAR _cur_ranking = [Rank]
VAR _result = IF(ISBLANK(__selected_value)|| (_cur_ranking>=__bef && _cur_ranking<=__aft),1)
RETURN
_result

 

Finally, put the measure in a visual object-level Filter to filter the data to be displayed only if the measure is 1:

 

The page looks like this:

 

If you have any other questions please feel free to contact me.

 

The pbix file is attached.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

tomrollinson's avatar
tomrollinson
Frequent Visitor
2 years ago

Thanks Anonymous - I have worked through your suggestions and it is has got me 95% of the way there for what I need. I have tried altering the above but I am struggling to get what I require. Is there a way to modify the above so that you can have the team name in a slicer, rather than needing the rank in the slicer?

 

Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi tomrollinson ,

     

    In the same way, you create a separate unjoined table for the team name inside the slicer.

     

    Something like this:

    Table 2 = DISTINCT('YourTable'[team name])

     

    The logic behind this remains the same.

     

    I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    • tomrollinson's avatar
      tomrollinson
      Frequent Visitor
      2 years ago

      Thank you Anonymous - I have just had another look using your suggestions above and have got it to work as I needed. Thanks for your support.