We need to use a slightly different formula. What if I wanted the slicer to also apply to the bar chart. Here is what the filtered view looks like, only two of the 4 show up in the bar chart. In some cases this is exactly the functionality you want. When you look at the table values ranked less than 10 overall will not be included in the chart. However, when I filter by Off-Topic only two values remain in the chart. This applies the top 10 filter only to the chart which is what we want. As you can see below, we pick the responses less than or equal to ten. In order to get the top responses in the bar chart we need to add Poll Response Rank to the Chart Filter (Not the view filter) for the bar chart. In my report, I will be adding a Slicer on Category Name (all Polls belong to a Category), a bar chart with Response Count by Poll Question, a Pie Chart with Response Count by Category, and a table which shows the raw rank value for each question. This applies a rank value in ascending order to the Polls by Response Count. On the Poll table, which is joined to the Poll Response table by Poll ID, I added the following calculation to give rank values to the Polls. Using that as the starting measure we add the ability to get the top 10 responses by Poll. We have a Poll Response Count calculated measure: I am using my Poll solution from the My Vote application built for Modern Apps Live. I will walk through the process so you can see both ways and how I got to the eventual solution.īoth solutions use the RANKX function in Power Pivot. So, now my top 10 charts would have fewer than 10 results. All tools are able to handle creating a calculation that will show the top 10 for a set of criteria, however, when the data was dynamically filtered on the page, only top 10 values that from the original measure that also worked in the filter worked. I was working with Power View and a couple other BI tools to see if this was possible. If you click a pie slice in a chart, filter the top 10 at the same time. The problem I was trying to solve was creating a top 10 chart in Power View that showed the top 10 in context of what was filtered in the Power View report. I used information from his post on Dynamic TopN Reports via Slicers, Part 2 to solve my problem which I will describe next. His book has been a great resource as I work through fun with DAX.
Before I get too far in the weeds, I have to recognize Rob Collie, his blog ( ) and his book DAX Formulas for PowerPivot: The Excel Pro’s Guide to Mastering DAX.