Forum Discussion

Oros's avatar
Oros
Post Prodigy
5 years ago

Exclude items from TOPN

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. 
Need Help

16 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Oros
      Post Prodigy
      5 years ago

      Hi Anonymous ,

       

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Oros 

         

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

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    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's avatar
      Oros
      Post Prodigy
      5 years ago

      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's avatar
        v-luwang-msft
        Community Support
        5 years ago

        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

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    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