How to dynamically Filter TOP N based on RANKX Slicer in Power BI

How to Filter TOPN data dynamically based on RANKX Slicer in Power BI
Suppose we have the data model in our Power BI Report as follows

Now we discuss in detail about Dynamically Filtering TOPN based on RANKX Slicer in Power BI

Phase - I :

First, we will create a Rank which will dynamically apply the Rank based on the Slicer Selection.

Here we use two Dimension tables "DimRegion" and "DimCustomers" which will evaluate first then apply the Rank by the "Net_Sales" of the Related Table "FactSalels"

We will use the following expression for Ranking.

RankBySelect = IF(ISBLANK(SUMX(RELATEDTABLE(FactSales),[Net_Sales])),BLANK(),
RANKX(
CROSSJOIN(ALLSELECTED(DimRegion),ALLSELECTED(DimCustomers)),
SUMX(RELATEDTABLE(FactSales),[Net_Sales]),,0))

Output :

Note :
In the above, since the Slicer "FiscalQuarter" is belongs to the "FactSales" , so that it was not used in the CROSSJOIN(ALLSECTED()) Expression. If it belongs to a another related table then we should include that in the Expression to Evaluate.

Phase - II :
Next we will create and load a TopNtable with two fields TopNname , TopNvalue as follows

Next we will create a Measure "TopNselect" to read the value from the above "TopNtable"

TopNselect = IF(HASONEVALUE(TopNtable[TopNname]),VALUES(TopNtable[TopNvalue]),MAX(TopNtable[TopNvalue]))

Now we will see "TopNselect" measure value on a Card based on the selection of the Slicer based on the "TopNtable[TopNname]"

Suppose if we select the two or more values in the Slicer, then the Max value in selection will be return by the "TopNselect" measure.

In case if we "Select All" or don't select any thing in the Slicer, then the Max value in Slicer will be return by the "TopNselect" measure.

                             

Phase - III :

Finally, we create a Measure "NetSales_ByRank" which will return the Net_Sales values by comparing the Slicer "TopNselect" measure value with the Rank measure "RankBySelect" value.

NetSales_ByRank = IF([RankBySelect]<=[TopNselect],SUMX(RELATEDTABLE(FactSales),[Net_Sales]))

Output :

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------