Top N by value with Year parameter

ephilip004 · ‎01-29-2018

Hi all,

I'm new to the Power BI community and DAX so I may be missing something simple here. I have a table with different metrics tracked for different oil wells across time. Fields include a unique well ID, operator, year, and other metrics. I want to filter my visuals for the top 25 operators that have the highest number of wells within 2016 and 2017. Right now I have used the visual level filters to only show the top 25 operators by number of wells, but this is for all years. I tried using a slicer to restrict the years to 2016 and 2017, but this takes the original top 25 operators across all years and then pares that list down to less than 25 operators. I am trying to get a list of the top 25 operators within 2016 and 2017. Any suggestions on how I can do this? If it includes DAX code, could you provide a simple explanation of how to use that as well?

Any help is much appreciated!

Rfranca · ‎01-29-2018

Hi, @ephilip004

https://msdn.microsoft.com/pt-br/library/gg492185(v=sql.120).aspx

Use-of-rankx-in-power-bi-measures

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

Best Regards,
Rfranca

ephilip004 · ‎01-29-2018

I tried using the Rankx function but it returned 1 for every row, since it is counting the number of wells for each well. I want to aggregate by operator first, then only looking at 2016 and 2017, determine the top 25 operators by well count. I have created another Excel file manually ranking the operators for the correct years from a pivot table, but I would like to know if it is possible to do straight through Power BI.