Forum Discussion
Filter on the greatest value
Hi guys,
I am trying to filter data of a list to only display the greatest number.
Here is what I have in my Excel data:
ratio 2019
| 55% |
| 82% |
| 61% |
| 0% |
| 113% |
| 163% |
| 92% |
| 160% |
| 118% |
Then in PBI, I just want the greatest value (in this case: 163%) to appear.
I was trying to use the new filter future but I only find filters like "is greater than" and not "the greatest value".
Do you guys have any idea?
Thank you for your help!!!!!!
Rémy
Hi remychag,
I created the following measure:
HighestRatio = MAX('Table'[Ratio 2019])This will find your highest ratio, then you could display that as a card to show the highest ratio or you could use this to filter other visuals.Hope this helps! 🙂Yes, there are two ways you could do this. For both visuals, you would start with a table visual. The first way is to create a table with the ratio and country then filter the visual by the top value as shown below:
The second way is to create a table with only the country field, filter on top N just like before, then change the visual to a Card visual. For some reason, the card visual does not let you filter on topN, but if you start with a table visual, it will. I think the cards look a lot nicer than the table view, so I would recommend doing this. An example of this is below.
Let me know if you have any other questions!
4 Replies
Hi remychag,
I created the following measure:
HighestRatio = MAX('Table'[Ratio 2019])This will find your highest ratio, then you could display that as a card to show the highest ratio or you could use this to filter other visuals.Hope this helps! 🙂Hi hwegman,
Thank you for your help, it works!!! (I'm not super familiar w/ DAX)
One more question: with each ratio goes a country, ex:
Ghana 55% Niger 82% Liberia 61% Sierra Leone 0% Togo 113% Senegal 163% Mali 92% Guinea 160% Ivory Coast 118% Under the biggest ratio, I would like to display (automatically) the correpsonding country and that's not possible with "Card" visualization.
Do you have any idea on how to do it please?
Rémy
Yes, there are two ways you could do this. For both visuals, you would start with a table visual. The first way is to create a table with the ratio and country then filter the visual by the top value as shown below:
The second way is to create a table with only the country field, filter on top N just like before, then change the visual to a Card visual. For some reason, the card visual does not let you filter on topN, but if you start with a table visual, it will. I think the cards look a lot nicer than the table view, so I would recommend doing this. An example of this is below.
Let me know if you have any other questions!
