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...
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.

     

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!