cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justinMcC
Frequent 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
Highlighted
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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

justinMcC
Frequent 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 Solution Sage
Solution Sage

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
Frequent 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
Frequent 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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors