Forum Discussion

Dude010101's avatar
Dude010101
Frequent Visitor
6 years ago
Solved

User enters full zip code as filter -> All zip codes beginning with 1st + 2nd letter to be shown

Hi,

 

I already shortened/ grouped the zip codes by their 1st and 2nd letter (1234 => 12).

Now I would like the user to be able to enter "1234" as a filter but the result showing all rows with the "12" group.

 

*edit* I already discovered filter groups but it would take a bit long to group thousands of zip codes. Maybe there is a more dynamic way.

 

Thanks a lot in advance!

 

 

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Dude010101 ,

     

    You need to create a new table as a slicer ( no relationship ). Then you could use LEFT() function to get the first and second letter.

    Measure =
IF (
    LEFT ( SELECTEDVALUE ( 'Fact_Table'[Column1] ), 2 )
        = LEFT ( SELECTEDVALUE ( 'Slicer_Table'[Column1] ), 2 ),
    1,
    0
)

    Here is the result. You could set the rule in the filter to show the data whose measure =1. Or you could edit this measure to show data directly.

    Here is my test file for your reference.

     

3 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Dude010101 ,

     

    You need to create a new table as a slicer ( no relationship ). Then you could use LEFT() function to get the first and second letter.

    Measure =
IF (
    LEFT ( SELECTEDVALUE ( 'Fact_Table'[Column1] ), 2 )
        = LEFT ( SELECTEDVALUE ( 'Slicer_Table'[Column1] ), 2 ),
    1,
    0
)

    Here is the result. You could set the rule in the filter to show the data whose measure =1. Or you could edit this measure to show data directly.

    Here is my test file for your reference.

     

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    Hi Dude010101 ,

     

    If I understand your query correctly; then this can be achieved by creating an additional column in your data-set for "shortened" zip code.

    In Query editor you can achieve this easily.

    Duplicate your Zip Code column --> Select your duplicated "Zip Code" column --> click on "Extract" option on the top --> extract first 2 characters of your duplicated Zip Code column --> Rename it to "Shortened Zip Code"

     

    If this doesn't solve your issue, It will be better to have a screenshot of current scenario and the expected output.

     

    If this helps please give Kudos and mark it as a Solution! 🙂

     

    Thanks,

    Pragati

    • Dude010101's avatar
      Dude010101
      Frequent Visitor
      6 years ago

      Hi,

       

      the function I'm looking for would be to select zip code "1234" in the slier but to actually select all zip codes starting with "12...".

       

      Greetings!