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.

     

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.