Forum Discussion

nish18_1990's avatar
nish18_1990
Icon for Helper III rankHelper III
9 months ago
Solved

Optimize the dax query for TOP N parameter

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 
ABC4
CDE5
FGH7
IJK6
MNO9
PQR10
  • Anonymous's avatar
    Anonymous
    9 months ago

    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

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    9 months ago

    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's avatar
      nish18_1990
      Icon for Helper III rankHelper III
      9 months ago

      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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 months ago

    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's avatar
    parry2k
    Icon for Super User rankSuper User
    9 months ago

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

     

    __Top = [Dynamic Top Measure]

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 months ago

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 months ago

      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.