Search code examples
powerbidaxmeasuretop-n

Power Bi: Top N visual level filter as Measure


Need a measure that will provide the same output as TopN visual level filter (than I can parameterize it).

The solution for simple cases provided HERE

But it doesn't work for more complicated cases...

EXAMPLE:

Don't work if you add any dimension that has Many to One Product Name relationship (Order Number for example).

Desired output: both tables (top and bottom) should be equal: enter image description here enter image description here

Example from screen available here HERE

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


Solution

  • A bit reworked solution from David Bacci:

    1. If you need just TopN Sales:

    TopnSalesAmount = 
    VAR param = [TopN Value]
    VAR topNTable =
        CALCULATETABLE (
            TOPN ( param, 'Product', [Sales Amount], ASC ),        
            ALLSELECTED ( 'Product'[Product Name] ),
            FILTER ( ALLSELECTED ( Sales ), [Sales Amount] <> BLANK () )
        )
    RETURN
        IF (
            NOT ( ISEMPTY ( Sales ) ),
            IF (
                SELECTEDVALUE ( 'Product'[Product Name] )
                    IN SELECTCOLUMNS ( topNTable, "a", 'Product'[Product Name] ),
                [Sales Amount]
            )
        )
    

    enter image description here

    2. If you need Rank for TopN Sales:

    rnkTopnSalesAmount = 
    IF (
      //ISINSCOPE ( 'Product Names'[Product Name]), -- depends, which one is used in visual
        ISINSCOPE ( 'Product'[Product Name] ) 
        && NOT ( ISEMPTY ( Sales ) ), 
    
        VAR ProductsToRank = [TopN Value]
        VAR topNTable =
            CALCULATETABLE (
                TOPN (
                    ProductsToRank,
                    ADDCOLUMNS ( VALUES ( 'Product'[Product Name] ), "@Amt", [Sales Amount] ),
                    [Sales Amount], ASC
                ),
                FILTER ( ALLSELECTED ( Sales ), [Sales Amount] <> BLANK () )
            )
        RETURN
            IF (
                SELECTEDVALUE ( 'Product'[Product Name] )
                    IN SELECTCOLUMNS ( topNTable, "a", 'Product'[Product Name] ),
                RANKX ( topNTable, [@Amt], [Sales Amount], ASC )
            )
    )
    

    enter image description here