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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rfranca
Resolver IV
Resolver IV

TOPN dynamic

I am using the formula to create a table

 

TabelaRank =CALCULATETABLE(VALUES(Sales[Pagamento]);TOPN (1; ALLSELECTED(Sales[Pagamento]); [Asset Rank]; ASC ))

 

TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])

 

but I want to make the 'n_value' parameter dynamic, but it does not. Only works with typed values.

Someone can help ...
thank you.

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Write the RANKX function instead and filter the result of the RANKX function with a crtieria of say <=3.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  

thank you... but....

the TOPN function in DAX the N_Value parameter does not work when the value comes from a measure, only accepts value typed.
I want to use a dynamic value for this parameter that comes from a SLICER.

How to make it happen?

Hi,

The N value in the TOPN() function can be the result of a measure.  Create a Table with 5 values (from 1 to 5).  Name that as Rank_values.  Create a slicer visual and drag the Values field from the Rank_values Table.  Select anyone value there.  Write this measure

Selected Top N = MIN(Rank_values[Value])

In your RANKX formula, the first inout should be [Selected Top N]

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi, @Ashish_Mathur 

 

Help me....

 

I need to paint a chart only with the items selected in the RANK.
For this I put together a list and if it is in the list it paints the chart.

 

https://1drv.ms/u/s!AjVxNwZurzV3g3tOY08NA7cMl7yC


Tks

Hi,

I cannot understand the language there.  Also, there are so many tables there that i do not know which one has the problem.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi,

create a parameter and give name as Top N by default select as slicer to page.

 

Create a Measure to calculate Dynamic Top N on any dimension like, Top N Customers, Top N City etc.

Before this we should create measure from data table here I am using orders table and creating measure for sale as below,

Sales Amount = SUM(Orders[Sales])

 

Create Top N Customers Measure as below under data table which is orders for now,

 

TopN Customers =

VAR SelectedTop = SELECTEDVALUE('Top N'[Top N])

RETURN

SWITCH(TRUE(),

    SelectedTop = 0, [Sales Amount],

    RANKX (

            ALLSELECTED(  Orders[Customer Name] ),

            [Sales Amount],,DESC

                )

                  <= SelectedTop,

        [Sales Amount]

)

 

Result:

Top N Dynamic.png

 

@Anonymous 

 

is not it.

I need to create a table with the top 3 of a Rankx list.

it is not to be changing the amount of rankx.
Review the initial question.

thank you;

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.