cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justinMcC Regular Visitor
Regular Visitor

Filter Table With Greater than, Less Than Formula

Hi

I need to create a filer on a report to will allow the user to dynamically filter an animal table based on animal age in months with greater than less than. Eg show me animals aged between 1 and 36 months

I have the age of animal age in months, my issue is that I don't know how to create a filter that will give me a list of the animals that match the filter criteria

Here is a demo of data I want to filter:

RAW TABLE DATA:

CLIENTID ANIMALID    AGE(MTHS)
client 1     animal 1         12
client 1     animal 2         15
client 2     animal 3         13
client 2     animal 4         19

END USER FILTER:
Age(mths) >= 12 and Age(mths) <= 14

END RESULT:

CLIENTID  ANIMALID    AGE(MTHS)
client 1     animal 1        12
client 2 animal 3            13

To Surmise I need a way to filter the data based on a greater than less than and a way for the user to select these parameters from a filter on the report

Thanks for any help, it is much appreciated

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Filter Table With Greater than, Less Than Formula

@justinMcC,

Although there is no between option in Power BI Service, when I publish PBIX file which has between option in numeric range slicer, the slicer works as expected and I set different values in the slicer, please review my screenshot.

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Re: Filter Table With Greater than, Less Than Formula

Can you just use a Slicer visualization for Age set to Between? It's the little drop down arrow next to the eraser on the Slicer visualization.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

justinMcC Regular Visitor
Regular Visitor

Re: Filter Table With Greater than, Less Than Formula

Hi Smoupre

 

I have tried the slicer visualisation filter however the only option available are list and dropdown. The Age(Mth) figures are formatted as a whole number

 

I did find this option in a number of articles, however in recent comments users have identified that this option no longer works (https://docs.microsoft.com/en-us/power-bi/desktop-slicer-numeric-range)

 

If the slicer needs 'fixed' by PowerBI then I am trying to find a workournd for this

 

Thanks for your help

jthomson New Contributor
New Contributor

Re: Filter Table With Greater than, Less Than Formula

It seems to be a preview option on mine, try playing with your settings to turn it on maybe?

justinMcC Regular Visitor
Regular Visitor

Re: Filter Table With Greater than, Less Than Formula

Hi @jthomson

 

Thanks for your reply, I did try the numeric slicer, it works great in preview, however when I publish the report it converts the numeric slicer to a list

 

As this option doesn't work on a published report, is there a workaround?

Moderator v-yuezhe-msft
Moderator

Re: Filter Table With Greater than, Less Than Formula

@justinMcC,

Although there is no between option in Power BI Service, when I publish PBIX file which has between option in numeric range slicer, the slicer works as expected and I set different values in the slicer, please review my screenshot.

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

justinMcC Regular Visitor
Regular Visitor

Re: Filter Table With Greater than, Less Than Formula

Thanks to everyone for your replies, I can use 'between' as a workaround for now.

 

Hopefully the numerical slicer will have full functionality available from preview to published soon

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors