Power Bi: how to parameterize Top N visual level filter

Question

Since PowerBI don't support Top N filter on page level, I want to use N as a parameter to change it at once per multiple visuals. Is it possible?

P.S.In this video (9:15) solution for more complex case is provided.

In the end of this article sample file available

Are you asking for just TopN or TopN and Others which is wildly more complicated depending on your use case. — Davide Bacci, Jul 23 '22 at 15:17
@David, Just Top N. Exactly as on screen, but using DAX code, NOT GUI — Denis, Jul 25 '22 at 08:46
@David, in some cases still don't work as estimated (output is not identical to TopN visual level filter). The details (example+file) shared in description above. Could you please help? — Denis, Jul 30 '22 at 12:02
It might be better to post a brand new question so others can also help debug your specific problem. — Davide Bacci, Jul 30 '22 at 12:38

Davide Bacci · Accepted Answer · 2022-07-25T15:07:15.603

2

Using the sample dataset, insert a new parameter.

Add a measure as follows:

Measure = 
IF(
    SELECTEDVALUE('Product'[Product Name]) IN 
    SELECTCOLUMNS( 
        TOPN(
            [Parameter Value], 
            ADDCOLUMNS( ALLSELECTED( 'Product'),"@Sales", [Sales Amount] ), 
            [@Sales]
            ), 
        "x", 
        'Product'[Product Name]), 
1)

Every visual you want affected by the TopN should have this filter.

That's it.

edited Jul 25 '22 at 15:07

answered Jul 25 '22 at 10:04

Davide Bacci

16,647
3
10
36

I tested and it works fine. I'll update with a screenshot. – Davide Bacci Jul 25 '22 at 13:26
[Parameter Value] is added automatically when you create a new parameter. – Davide Bacci Jul 25 '22 at 13:50
Could you please use source file without data refresh and share screen with slicer filters selected? (I just have much smaller totals and can't reproduce your result) – Denis Jul 25 '22 at 13:51
I used the source file you posted without refreshing. I'll upload somewhere for you now – Davide Bacci Jul 25 '22 at 13:53
1

https://wetransfer.com/downloads/29949ebb9f62c175ae13d6c919dbe6df20220725135411/d0455e – Davide Bacci Jul 25 '22 at 13:54
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/246740/discussion-between-denis-and-david). – Denis Jul 25 '22 at 14:17
1

Updated to ALLSELECTED() to account for user filters. – Davide Bacci Jul 25 '22 at 15:08
If I want to get not Top but Bottom N... As far as I understand, I just should add "ASC" in the end op TOPN.... But matrix don't return any rows then.... Could you please help? – Denis Jul 27 '22 at 10:06
The reason you get no rows returned is that you have lots of products with no sales. – Davide Bacci Jul 27 '22 at 10:27
Not exactly, because I put Sales>0 on visual filter. Moreover, if I put to matrix Measure - it displays ones correctly for products. But when I apply visual filter "is not blank" for measure, matrix becomes empty – Denis Jul 27 '22 at 10:55
I tested it and that is the reason. The filtering is happening within the topN. Change [Parameter Value] to a high number to check – Davide Bacci Jul 27 '22 at 11:03
Still not clear how to get Bottom 3 instead of Top 3... – Denis Jul 27 '22 at 11:09
1

Measure = IF( SELECTEDVALUE('Product'[Product Name]) IN SELECTCOLUMNS( TOPN( [Parameter Value], FILTER(ADDCOLUMNS( ALL( 'Product'),"@Sales", [Sales Amount] ), [@Sales] > 0 ), [@Sales], ASC ) , "x", 'Product'[Product Name]) , 1) – Davide Bacci Jul 27 '22 at 11:18

Denis · Answer 2 · 2022-07-30T13:16:39.637

From usability perspective it's preferable to return Sales Rank in measure.

Solution below is a copy/paste from SQLBI experts solution with minimal code changes ( ALLSELECTED ( 'Product'[Product Name] ) replaced by ALLSELECTED ( 'Product' ) ):

rnkSales = 
    IF (
        ISINSCOPE ( 'Product'[Product Name] ),
        VAR ProductsToRank = [TopN Value]
        VAR SalesAmount = [Sales Amount]
        RETURN
            IF (
                SalesAmount > 0,
                VAR VisibleProducts =                 
                    FILTER(     -- filters out data with no sales
                        CALCULATETABLE (
                            VALUES ( 'Product' ),
                            ALLSELECTED ( 'Product')   -- Use this if VisualFilterTopN equivalent required
                            //ALLSELECTED ( 'Product'[Product Name] ) -- Original code - returns TopN per dimension
                        ),
                        NOT ISBLANK( [Sales Amount] ) -- looks more universal then [Sales Amount]>0 (if calculation for Margin required, it could be negative)
                    )
                VAR Ranking =
                    RANKX (
                        VisibleProducts,                    
                        [Sales Amount],
                        SalesAmount
                    )
                RETURN
                    IF (
                        Ranking > 0 && Ranking <= ProductsToRank,
                        Ranking
                    )
            )
    )

Power Bi: how to parameterize Top N visual level filter

2 Answers2

Linked