Forum Discussion

nish18_1990's avatar
nish18_1990
Icon for Helper III rankHelper III
11 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 cal...
  • Anonymous's avatar
    Anonymous
    11 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
11 months ago

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

 

__Top = [Dynamic Top Measure]