Optimize the dax query for TOP N parameter

[nish18_1990]

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

[v-priyankata]

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.

[v-priyankata]

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.

[parry2k]

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

__Top = [Dynamic Top Measure]

[Anonymous · Accepted solution]

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

[parry2k]

@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 ] ) )
        )
    )
)

[nish18_1990]

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