Forum Discussion

captainlaw's avatar
captainlaw
Microsoft 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
Super 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

  • OwenAuger's avatar
    OwenAuger
    Super User
    6 years ago

    Ouail yes it is possible. The way I have done it in the past requires creating an "unpivoted" version of the table for the entity in question. This unpivoted table the contains all attribute-value pairs for each row of the original table.

     

    This article contains a good example:

    http://tinylizard.com/dynamically-changing-chart-axis/ 

     

    That article was written in the context of Power Pivot which doesn't allow bidirectional relationships. But since Power BI does allow bidirectional relationships, you can extend the method in that article by creating a bidirectional relationship between ProductMaster and ProductUnpivot (using table names from the article).

     

    Kind regards,

    Owen