Saturday, November 17, 2018

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 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog