Forum Discussion

aaarmstee67's avatar
aaarmstee67
Helper I
8 years ago
Solved

Dynamic TopN Filter with multiple column

I am working on a project. I need to create a dynamic top n which should work when different slicers are used. I used RankX which gives me the results i need when i group on only one column, eg clientgroup, but the issue arises when i add different columns, such as ClientGroup, SalesLocation, SalesPersonName, HoursWorked. The results is not right when i apply different slicers.  I tried to create a column for rownumber so that i can get the dynamic topn, that is if the row number can default to start from 1 with increment of 1 whenever any filter is applied. But this is becoming more difficult than i thought. Can anybody help. Urgent please. I have spent several days on this but to no avail.

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi aaarmstee67,

     

    Seems your conditions here are different from your previous sample. But you can adjust it to satisfy your requirements. I have sent the demo base on your data through Private Message. 

    selectedTopN =
VAR ranks =
    CALCULATE (
        RANKX ( ALL ( 'TESTPBI1'[contactname] ), CALCULATE ( SUM ( TESTPBI1[qty] ) ) ),
        ALLSELECTED ( TESTPBI1[companyname] ),
        ALLSELECTED ( TESTPBI1[country] ),
        ALLSELECTED ( TESTPBI1[Value] )
    )
RETURN
    IF (
        ISBLANK ( 'TopN'[TopN Value] ),
        ranks,
        IF ( ranks <= 'TopN'[TopN Value], ranks, BLANK () )
    )

    Finally, you can select the top N value from the slicer and filter out the blanks to show top N only.

    topn2

     

     

    Best Regards,

    Dale

8 Replies

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi aaarmstee67,

         

        Please delete the link if it contains sensitive data.

        The trick is the context will be filtered down to one row as a group. So all the rank is 1st because it's 1st in its own group. Please try to clear the context you don't need. 

        Ranks 2 =
CALCULATE (
    RANKX ( ALL ( TESTPBI1[contactname] ), CALCULATE ( SUM ( TESTPBI1[qty] ) ) ),
    ALL ( TESTPBI1[companyname] )
)

        Ranks 3 =
CALCULATE (
    RANKX ( ALL ( TESTPBI1[companyname] ), CALCULATE ( SUM ( TESTPBI1[qty] ) ) ),
    ALL ( TESTPBI1[contactname], TESTPBI1[Value] )
)

        Dynamic_Top_N_Filter_with_multiple_column2

         

        Best Regards,

        Dale