Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alextarki
Helper I
Helper I

Dynamic TOP N list

HI

 

I have investigated multiple demos and didnt find the answer for the following question :

Top N - How to enter dynamicly select the list of the first N records as the filter  TOPN is hardcoded to an individual value 

 

Select TOP N : 5     10   20 

 

Company                   Sales

A                                      100

B                                           50

C                                          30

D                                             5

1 ACCEPTED SOLUTION

Hi @alextarki ,

 

>>The filter should be by company rather then by sales measure 

 

Do you mean to show the data of the top n companies. And you need to add an index column for it:

Untitled picture.png

Create a topn table for slicer:

Untitled picture1.png

Create a measure for filter on the visual:

 

Measure =

VAR N =

    IF ( HASONEVALUE ( 'TopN'[top] ), SELECTEDVALUE ( 'TopN'[top] ), 3 )

RETURN

    IF ( MAX ( SALES[Index] ) < N, 1, 0 )

 

Untitled picture2.png

 

For more details ,please refer to https://community.powerbi.com/t5/Desktop/Dynamic-TopN-reports-in-Power-BI/td-p/122091

Best Regards,

Dedmon Dai

View solution in original post

6 REPLIES 6
nandukrishnavs
Super User
Super User

@alextarki 

 

CompanySales
A100
B150
C200
D250
E300
F350
G400

 

 

 

salesmeasure = SUM(SalesTable[Sales])

 

 

create a what-if parameter

what.JPG

Create a DAX measure

 

 

Status = 
var _rank=CALCULATE(RANKX(ALL(SalesTable),SalesTable[salesmeasure],,DESC,Dense))
var _result= IF(Parameter[Parameter Value]>=_rank,"show","hide")
return _result

 

 

You can use the above measure in the visual level filter. 

 

Capture.JPG

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

 


Regards,
Nandu Krishna

 Thanks

 

The filter should be by company rather then by sales measure 

 

IF I select 30 then filter should deliver the list of 30 companies

Hi @alextarki ,

 

>>The filter should be by company rather then by sales measure 

 

Do you mean to show the data of the top n companies. And you need to add an index column for it:

Untitled picture.png

Create a topn table for slicer:

Untitled picture1.png

Create a measure for filter on the visual:

 

Measure =

VAR N =

    IF ( HASONEVALUE ( 'TopN'[top] ), SELECTEDVALUE ( 'TopN'[top] ), 3 )

RETURN

    IF ( MAX ( SALES[Index] ) < N, 1, 0 )

 

Untitled picture2.png

 

For more details ,please refer to https://community.powerbi.com/t5/Desktop/Dynamic-TopN-reports-in-Power-BI/td-p/122091

Best Regards,

Dedmon Dai

@alextarki What is the logic for getting the 30 companies? 


Regards,
Nandu Krishna

nandukrishnavs
Super User
Super User

@alextarki 

 

Refer to this similar post https://community.powerbi.com/t5/Desktop/Top-n-Filter-with-Dynamic-fields/m-p/901378



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂


Regards,
Nandu Krishna

Unfortunately, this topic is not related to my point I need dynamic list of value : I select TOP 10 it shows 10 records ,

I select TOP 20 - it shows 20 records 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors