Slicer based on Rank function

DAX DAX Calculations
1

Hi All,
I’m trying to implement a slicer based on calculated column which is Rank function and below is the screenshot of the data i’m trying to implement.

any pointers please let me know.

image
image1538×200 8.71 KB

Regards,
Ravi

2

some sample data would be helpful here, to assist in understanding how you have calculated the rank being used, etc.

Also, I see your example is created in Excel, but is that what you will be using for your final project, or will this go into a PBIX file?

1 Like
3

Thanks @Heather for the reply.

rank is based same table with 2 columns as unique values based on date e.g:

RANKX(FILTER(‘Table1’,‘Table1’[order number]=EARLIER(Table1[order number])&&Table1[order type]=EARLIER(Table1[order type)),Table1[order Date],DESC,Dense) .

i have 8 report pages which i have give them a slicer option to either select all records or latest records.i know we can implement bookmarks but for 8 pages this might impact performance so thought to have slicer value for selection.

hope this helps.

4

Please upload a work-in-progress PBIX file (using sample data, if necessary) and a mock-up in Excel of exactly what you’re trying to achieve.
Greg

5

Hi @Ravi85, we’ve noticed that no response has been received from you sinceNovember 30th.

The experts are waiting for your masked demo pbix file.

This will help you in getting a strong solution faster.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

I have used below method for filtering by all transaction and dynamic Topn by using the below method:
Create a table : TOPN
TOPN = GENERATESERIES(1,10,5)

SelectedTopNNumber = IF(HASONEVALUE('TOPN’[N]),Min(‘TOPN’[N]),DISTINCTCOUNT(DateTable[PK_Date])) //
you may give your date table details

TopOppNumber = RANKX(ALLSELECTED(View_OPP_POWERBI[No_]), [OPP_VALUE],DESC) // I have created this calculation for ranking the opportunity numbers based on its value… you may use calculation based on your ranking requirements

By default, the slicer will be in ‘All’ option and you will get all the data

Create a below calculation :
ShouldOppNoAmount = IF([TopOppNumber]<=[SelectedTopNNumber],1,0)
Filter the above calculation in the required visual
Once it is done, the data will filter based on your ranking :

The above logic you can use by selecting rank 1 for ‘latest transaction’ and ‘select all’ for all transactions

I have attached a word document with visuals for your reference…

I think this will be useful to you…

Regards
S Uma

7

thanks @UmaSuresh for sending the logic and i couldn’t find the word document attached please can you re-attach it.

8

PBIxfile.pbix (125.1 KB)
Dear @Ravi85

Please find attached the pbix file with sample data

when you filter by top1, you will get latest transaction
When you filter by all, you will get all the transaction

Hope this will be helpful

Regards
S Uma

9

Thank you so much for that detailed answer @UmaSuresh

We hope this helped you @ @Ravi85:slight_smile:

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

10

Hi @Ravi85, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.