Exclude items from TOPN

[Oros]

Hello.

I have a report with TOP N.  I created a new TOP TABLE and put the index top 5, top 10 and top 15.  I created a measure to add in the filter of the report:

ITEM TOP in SelectedN = IF([ITEM RANKING] <= [ITEM TOPSELECTED],1,0)

How do you exclude from the TOP N certain items while maintaining the selected top number of items?

I tried to exclude an 2 items from any selected top items, but the selected top does not replace them.

For example, if top 5 is selected and 2 items have already been filtered out, the top 5 only shows 3 items and do not replace the excluded 2 items.

Thanks. 

[v-luwang-msft]

Hi @Oros ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[v-luwang-msft]

Hi @Oros ，

Due to without your data ,I create a sample:(rank the below data ,and except 55,43)

base table:

index table:

Step 1, use the below dax to create a new column:

ITEM RANKING = 
IF (
    Product_Table[Sales] = 55
        || Product_Table[Sales] = 43,
    BLANK (),
    RANKX (
        FILTER (
            Product_Table,
            Product_Table[Sales] <> 55
                && Product_Table[Sales] <> 43
        ),
        Product_Table[Sales],
        ,
        DESC,
        DENSE
    )
)

You will get the below:

Step 2, use the following dax to create a new measure:

ITEM TOP in SelectedN =
IF (
    MAX ( Product_Table[ITEM RANKING] )
        <= SELECTEDVALUE ( 'ITEM TOPSELECTED'[INDEX] ),
    MAX ( Product_Table[ITEM RANKING] ),
    BLANK ()
)

Then you will see :

You could download my pbix file if you need.

Wish it is helpful for you!

Best Regards

Lucien

[Oros]

Hello @v-luwang-msft ,

Thank you for your reply.

I am getting the error a circular dependency was detected when I followed Step 1.

Any ideas?

Thanks.

[v-luwang-msft]

Hi @Oros ,

Could you pls share your pbix file ?And change confidential data to sample data.

This may vary due to differences in the underlying data and will need to be adjusted to your specific file. 

Best Regards

Lucien

[Oros]

Hi @v-luwang-msft ,

Thank you for your reply.  Unfortunately the data is too big.

[v-luwang-msft]

Hi @Oros ,

Test the below,and don not forget change the tablename (if Item data type is number) :

TEST =
RANKX (
    FILTER (
        ValueEntries,
        ValueEntries[Item] <> 1623
            && ValueEntries[Item] <> 5435
    ),
    ValueEntries[Sales],
    ,
    DESC,
    DENSE
)

 if Item data type is text:

TEST =
RANKX (
    FILTER (
        ValueEntries,
        ValueEntries[Item] <> "1623"
            && ValueEntries[Item] <> "5435"
    ),
    ValueEntries[Sales],
    ,
    DESC,
    DENSE
)

Wish it is helpful for you!

Best Regards

Lucien

[Oros]

Hi @v-luwang-msft ,

Unfortunately the first TES measure gives this error.

The second TEST measure did not give error BUT lost all the top items.  All items show instead of just the selected top (5, 10, 15, 20...).  Hundreds of items show.

Any ideas?

Thanks.

[Vera_33]

Hi @Oros 

You are using this measure in Filters pane to filter [ITEM TOP in SelectedN]=1 to display the Items you need, modify this one a little bit

ITEM TOP in SelectedN =
IF (
    SELECTEDVALUE ( yourTable[yourItemColumn] )
        IN { "yourItem1Name", "yourItem2Name" },
    0,
    IF ( [ITEM RANKING] <= [ITEM TOPSELECTED], 1, 0 )
)

[Oros]

Hi @Vera_33 ,

Thank you very much for your reply.  It works in filtering out the items to be excluded.  But it is not replacing those excluded items.

For example if the Top 5 items are A, B, C, D, E and you would like to exclude items D and E, the TOP 5 should be items A, B, C, F, G  and not just A, B, and C.  The Top 5 items should still show 5 items and not 3 itmes.

The same thing if Top 10 is selected, if any there are two (2) items excluded, the Top 10 should take the 11th and the 12th to replace the excluded items.

Thanks again.

[Vera_33]

Hi @Oros 

I misuderstood it, so when you do RANKX, you need to filter the items out first

[Oros]

Hi @Vera_33 ,

Where exactly should I filter the items here?  Thanks.

ITEM RANKING = RANKX(ALL(Product_Table[Item]),Product_Table[Sales],, DESC)

[Vera_33]

Hi @Oros 

try it

ITEM RANKING = RANKX(FILTER(ALL(Product_Table[Item]),Product_Table[Item])<>"yourItem1"&&Product_Table[Item])<>"yourItem2"),Product_Table[Sales],, DESC)

[Oros]

Hi @Vera_33 ,

It does not look like it's working.  Maybe it's just missing some characters?

Attached is the screenshot directly from Power Bi.  Just for the purpose of this example, I replaced the original table name to 'Product_Table'. Please ignore the spaces atthe beginning and end of 'Product_Table'.  But how it looks in Power Bi with the original table name is the same.

Thanks.

[Vera_33]

Hi @Oros 

Yes, && is missing between the two items, highlighted here

ITEM RANKING = RANKX(FILTER(ALL(Product_Table[Item]),Product_Table[Item])<>"yourItem1"
&&
Product_Table[Item])<>"yourItem2"),Product_Table[Sales],, DESC)

[Oros]

Hello @Vera_33 ,

Thank you for your reply.

I tried it and still did not work.  The second item to exempt is greyed out.

Please see attached.

Thanks again.

[Vera_33]

Hi @Oros 

There is a hint, the syntax "," is not correct. You may to go to format your DAX code and try to find out your problem. Or I will pm you my email, you can send me your DAX measure or sample file if you want.

https://www.daxformatter.com/