Forum Discussion

captainlaw's avatar
captainlaw
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago
Solved

Top N with Parameter?

Hello Community, I’m curious if there's a workaround to pass a parameter for Top N filtering? I haven’t seen a good example, so wondering if you know some resources on this.  Customer would like to ...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    captainlaw

     

    You would only need to add additional ALLSELECTED parameters to CALCULATETABLE if you are putting those additional columns on axes (or rows/columns) of the visual itself. If you are simply adding more slicers, then the existing measure would be fine.

     

    However, a better way of defining the measure to capture all possibilities would be:

     

    MyTopN3 = 
CALCULATE (
    SUM ( LAWCrime[Violent crime total] ),
    KEEPFILTERS (
        CALCULATETABLE (
            TOPN (
                [SelectedN],
                VALUES ( LAWCrime[State] ),
                CALCULATE ( SUM ( LAWCrime[Violent crime total] ) )
            ),
            ALLSELECTED ()
        )
    )
)

    This uses ALLSELECTED () without arguments to ensure that the ranking is carried at the total level of the visual (this also means you can change ALLSELECTED ( LAWCrime[State] ) to VALUES ( LAWCrime[State] ).

    If you want to rank by something other than state then just change the red part.

     

    Cheers,

    Owen :)

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
9 years ago

Hi captainlaw

 

Just a couple of tweaks required.

I've fixed the measure so that it replicates the behaviour of the visual level TopN filter, and named the measure MyTopN3.

 

PBIX here

 

 

MyTopN3 = 
CALCULATE (
    SUM ( LAWCrime[Violent crime total] ),
    KEEPFILTERS (
        CALCULATETABLE (
            TOPN (
                [SelectedN],
                ALLSELECTED ( LAWCrime[State] ),
                CALCULATE ( SUM ( LAWCrime[Violent crime total] ) )
            ),
            ALLSELECTED ( LAWCrime[Year] )
        )
    )
)

Key points:

 

  1. TOPN 2nd argument: Used ALLSELECTED ( LAWCrime[State] ) within TOPN. This is because you want to see the TopN from States selected on the slicer rather than all States.
  2. TOPN 3rd argument: Wrapped in CALCULATE. The expression in the 3rd argument is calculated in the row context of each row of the table in the 2nd argument. CALCULATE (or a measure with an implied CALCULATE) is needed for context transition (to convert the row context to filter context), to correctly calculate the SUM in the context of the current State.  
  3. Wrapped the TOPN in a CALCULATETABLE(..., ALLSELECTED ( LAWCrime[Year] ) ). This ensures the States are ranked based on their total over the entire range of years selected (same as the chart on the left).

Regds,

Owen

 

  • captainlaw's avatar
    captainlaw
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    You are very helpful Owen.

    If I have additional relationships to calculate the Top N, such as City or Gender... etc, do I simply add it to the end of this clause -

     

    ALLSELECTED ( LAWCrime[Year]

    ,ALLSELECTED ( LAWCrime[Gender] 

    ,ALLSELECTED ( LAWCrime[City] 

    ... etc.

     

    Thank you!

    • OwenAuger's avatar
      OwenAuger
      Icon for Super User rankSuper User
      9 years ago

      captainlaw

       

      You would only need to add additional ALLSELECTED parameters to CALCULATETABLE if you are putting those additional columns on axes (or rows/columns) of the visual itself. If you are simply adding more slicers, then the existing measure would be fine.

       

      However, a better way of defining the measure to capture all possibilities would be:

       

      MyTopN3 = 
CALCULATE (
    SUM ( LAWCrime[Violent crime total] ),
    KEEPFILTERS (
        CALCULATETABLE (
            TOPN (
                [SelectedN],
                VALUES ( LAWCrime[State] ),
                CALCULATE ( SUM ( LAWCrime[Violent crime total] ) )
            ),
            ALLSELECTED ()
        )
    )
)

      This uses ALLSELECTED () without arguments to ensure that the ranking is carried at the total level of the visual (this also means you can change ALLSELECTED ( LAWCrime[State] ) to VALUES ( LAWCrime[State] ).

      If you want to rank by something other than state then just change the red part.

       

      Cheers,

      Owen :)

      • Ouail's avatar
        Ouail
        Frequent Visitor
        6 years ago

        Hello Owen,

         

        Is there a way to parametrize the red part using a slicer ? To change the "subject" of the Top N.
        Use case : I have multiple bar charts with different axis columns where I want to use the same TopN measure.

         

        Regards,

        Ouail