Forum Discussion

justinMcC's avatar
justinMcC
Frequent Visitor
8 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    8 years ago

    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.



    Regards,
    Lydia

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    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.

    • justinMcC's avatar
      justinMcC
      Frequent Visitor

      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's avatar
        jthomson
        Solution Sage

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