Filter top N rows based on value not working as values are duplicated.

[Anonymous]

I have the following dataset.

Company	Rank	No
ABC	10	A1
PQR	10	A2
LIE	10	A3
IHUIG	10	A4
IU	10	A5
OI	10	A6
WHEFW	10	A7
SDBV	10	A8
UER	10	A9
WUIE	10	A10
WUIEF	20	B1
DIFO	20	B2
WERUI	20	B3
SIUDYF	20	B4
CNSJKD	20	B5
ISOEUF	20	B6
QIWUO	20	B7
SXCUYSI	20	B8
OWIERQ	20	B9
IFIHI	20	B10

I wanted to filter top 12 Companies based on Rank. As the data is connected using direct query (SQL database), I am unable to use Rank or any such dax. When I tried adding a top 12 filter for Company based on Rank, the table visual looks as below.

Company	Rank
ABC	10
PQR	10
LIE	10
IHUIG	10
IU	10
OI	10
WHEFW	10
SDBV	10
UER	10
WUIE	10
WUIEF	20
DIFO	20
WERUI	20
SIUDYF	20
CNSJKD	20
ISOEUF	20
QIWUO	20
SXCUYSI	20
OWIERQ	20
IFIHI	20

Any help would be very helpful. Thanks in advance !

[v-yingjl]

Hi @Anonymous ,

1. If the dataset is all of your data, perhaps you need to create a index column in the data source manually and use topN filter to filter bottom 12 fields like this:

2. If the dataset has other fields and rank value, you can create a rank measure like this and use topN filter:

Measure = RANKX(ALL('Rank_table'),CALCULATE(SUM(Rank_table[Rank])),,ASC,Dense)

 

Here is my whole sample data table:

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You can use Rank with Skip option, But in

example

Rank = RANKX(all(Company[Company]),[Sales],,desc,skip)

Sumx(Filter(Values(Company[Company]),[Rank ]<=12),[sales])

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Anonymous]

Hi @amitchandak, Thanks alot for your help.

But since we are using direct query method to connect with the dataset, we are unable to use Rank function.