Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filtered threshold slicer

Hi,

 

I know this is easy with the filters pane, but a corporate decision not to use that limits me here:

 

I am massively oversimplifying my data for the purposes of explanation... I have the following tables:

Sales - a list of sales (assume 1 row = 1 sale for 1 product)

Returns - a list of returns

Product - joined to Sales and returns

Dates - joined to Sales and Returns based on date of sale and date of return.

 

I have a slicer for dates and 3 measures,

Sales Count = COUNTROWS(Sales) 

Return Count = COUNTROWS(Returns) 

Return Rate = DIVIDE([Sales Count], [Return Count])

which are filtered by the date slicer, and shown on a bar chart, by product.

 

I want to add a slicer which allows me to set a minimum threshehold for sales, so for example any product with only 5 sales lines will be excluded. But I can't use a slicer based on the Sales Count because you can't have a slicer based on a measure.

 

I've tried adding a column on the Product table: COUNTROWS(RELATEDTABLE([Sales])) and then used that as "Greater Than or Equal To" range Slicer, but this then ignores the date slicer and I need it to be filtered to the dates.

 

How can I accomplish this?

 

2 Replies

  • v-xicai's avatar
    v-xicai
    Community Support

    Hi Anonymous ,

     

    I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

     

    Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

     

    Best Regards,

    Amy 

    • Anonymous's avatar
      Anonymous
      Not applicable

      v-xicai  My desired result is the same as if i had added a filter to the filter panel for Sales Count, so I can change it to Sales Count > 10 for example. But without using the filter panel. You cannot use measures as a filter in a slicer. I need to do this.