Top N by value with Year parameter

[ephilip004]

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]

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]

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.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Also, show the expected result there.

[ephilip004]

Here is the link to the report:

https://app.powerbi.com/groups/me/reports/94a2c51c-2bf3-4162-b84e-ff7642292065?ctid=513294a0-3e20-41...

[Ashish_Mathur]

Hi,

I cannot download the file from that link.

[ephilip004]

Do you have Power BI Pro? I can share access via email if you give me your contact info. Otherwise what should I use to share the file?

[anandav]

@ephilip004,

If you are already using visual filters, can you use the same for Dates?

You need to select 'Adavance' filter. See image below.

[Rfranca]

HI, @ephilip004

YOU CAN SEND A SAMPLE OF DATA TO APPLY THE SOLUTION.