Displaying top x with Rankx

[michaeljc70]

So, I've searched and found several topics on getting the Top X using RankX.  However, I am not sure where/how to use this.  I am new to Power BI (SSRS background).  

I am using a SQL Server query as my datasource.   When someone picks something in my slicers, I'd like to display the top 5 relevant to the filter.  I have another page that shows all data, so don't want to modify the SQL query.

I am not sure exactly how RANKX works (the reference here is kind of light https://msdn.microsoft.com/en-us/library/gg492185.aspx) and where I put it and what it affects.  Is this something I add to the query under advanced (doubtful as that would affect all pages)?  Or do I create a new measure column with the RANKX?  

The solutions I saw include things not mentioned in the Rankx reference like ALLSELECTED or FILTERED which are other functions I'm sure, but not sure how this all goes together.

Thanks

[Greg_Deckler]

=RANKX(ALL(Products), SUMX(RELATEDTABLE(InternetSales), [SalesAmount]))

You want to put a formula similar to that in a custom column. So, after you import the data into your data model, go to the middle icon on the left nav pane (the one that looks like a table) and then choose your data table, then choose the "Modeling" tab at the top and select "New Column". Paste in the formula. What the formula from the reference is doing is:

ALL(Products) - Gets rid of any context filters in order to make sure that all products are ranked. You may not want this in your situation. ALL is a filter function in DAX. Products is the name of the table that we want to create a ranking on. You would create this custom colum in the Products table. Substitute your own table name for Products.

SUMX(RELATEDTABLE(InternetSales), [SalesAmount])

This is using a related table, InternetSales and the column called "SalesAmount" from that table in order to do the ranking. In the example, InternetSales table contains transactions of product sales, so we have to sum them together. SUMX is a DAX function and allows you to adjust the context of the SUM function, in this case essentially switching tables to pull back rows from InternetSales that are related based upon the Product name/id. What is not provided here is that there is obviously some relationship between the Products table and the InternetSales table, likely based upon a product ID field.

Hope this helps. If you can post some sample data, could probably give you a better solution that is specific to your use case. 

[michaeljc70]

Thanks for the response.  

I did play around some more and discovered that I cannot use a custom formula because I am using DirectQuery.  It seems I have to incorporate the rankings in my query.  Please correct me if I am wrong.  

[Greg_Deckler]

You are correct.