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([IT...
Need Help
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

    • Oros's avatar
      Oros
      Post Prodigy
      5 years ago

      Hi v-luwang-msft ,

       

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

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

        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