topic Optimize the dax query for TOP N parameter in DAX Commands and Tips

Optimize the dax query for TOP N parameter

nish18_1990 — Tue, 18 Nov 2025 16:07:55 GMT

Hi ,

I want to have ranking based on TOPN parameter selection . If i select 3 in Top N RES Parameter then it should show be top 3 Responsible Legal Entity based on measure [#OnlyOpen]. The below calculation is working perfectly fine but the issue i am facing is performance . If i select any other number in the parameter TOP N RES then the table is loading for data for more than 1 min . How can i optimize this dax calculation for increasing the performance .

TopN RES =

Var Ranks =

CALCULATE(

RANKX(

ALL('My_Main_Table'),

CALCULATE(

[#OnlyOpen],

ALLEXCEPT('My_Main_Table',My_Main_Table[Resp Risk Unit Level 2 Name])),,

DESC,

Dense

)

RETURN

IF (

ISBLANK ( 'Top N RES Parameter'[Top N RES Parameter Value] ),

ranks,

IF ( Ranks <= 'Top N RES Parameter'[Top N RES Parameter Value] , [#OnlyOpen], BLANK () )

)

Data is something like this structure :

Responsible Legal Entity	Only Open
ABC	4
CDE	5
FGH	7
IJK	6
MNO	9
PQR	10

Re: Optimize the dax query for TOP N parameter

parry2k — Tue, 18 Nov 2025 17:28:14 GMT

@nish18_1990 try this:

Top N = VAR __Top = 5 RETURN CALCULATE ( SUM ( TopTest[Only Open ] ), KEEPFILTERS ( TOPN ( __Top, ALLSELECTED ( TopTest ), CALCULATE ( SUM ( TopTest[Only Open ] ) ) ) ) )

Re: Optimize the dax query for TOP N parameter

Anonymous — Tue, 18 Nov 2025 20:16:36 GMT

Hi @nish18_1990 ,

Please try below measures.

Measure 1: Top N Responsible (Boolean filter).

Top N RES Flag =
VAR N =
SELECTEDVALUE ( 'Top N RES Parameter'[Top N RES Parameter Value], 0 )
VAR TopNTable =
TOPN (
N,
ALL ( 'My_Main_Table'[Resp Risk Unit Level 2 Name] ),
[#OnlyOpen], DESC
)
RETURN
IF (
MAX ( 'My_Main_Table'[Resp Risk Unit Level 2 Name] ) IN TopNTable,
1,
0
)

Measure 2: Final Result

Top N RES =

IF ( [Top N RES Flag] = 1, [#OnlyOpen] )

If my response as resolved your issue please mark it as solution and give kudos.

Thanks,

Dinesh

Re: Optimize the dax query for TOP N parameter

nish18_1990 — Wed, 19 Nov 2025 05:10:19 GMT

Hi ,

I need dynamic parameter selection for Top N . Like if i select 3 then show me 3 res , if i select 5 then show me 5 res

Re: Optimize the dax query for TOP N parameter

parry2k — Wed, 19 Nov 2025 05:30:47 GMT

Yes you replace _Top variable with your dynamic selected top value, something like this

__Top = [Dynamic Top Measure]

Re: Optimize the dax query for TOP N parameter

v-priyankata — Mon, 24 Nov 2025 10:49:10 GMT

Hi @nish18_1990

Thank you for reaching out to the Microsoft Fabric Forum Community.

@parry2k @Anonymous Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

Re: Optimize the dax query for TOP N parameter

v-priyankata — Thu, 27 Nov 2025 08:24:48 GMT

Hi @nish18_1990

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.