Combining two columns in a slicer

[Dunner2020]

Hi there,

I have two columns located in two different tables. Tables do not have a direct relationship with each other. Both tables are dimension tables. The first table contains the city name and its code where as the second table contains the postal code. As one city can have multiple post codes, I want to show city code and postal code in a single slicer (in drop-down list) so that I can filter the report page based on city and postal code. Any help would be really appreciated. 

Data can be downloaded from here 

[Dunner2020]

@sevenhills is it a table ?

[sevenhills]

Yes, I mentioned as a common table and then do the slicer based on it.

If this is not what you want, sorry!

[Dunner2020]

@sevenhills, Thanks for the reply. I want "city - postal code"  in the slicer as drop-down list.

[sevenhills]

If it is me, I will do these steps.

1. Create a common table and have all the combinations of CityCode, Postal Code, Slicer City Postal Code

2. Link to the appropriate tables i.e., to the existing data tables

3. Create the slicer using "Slicer City Postal Code"

M language: 

let
    Source = Table.Combine({CityTable, #"PostalCodeTable"}),
    #"Changed Type" = Table.TransformColumnTypes(Source, { { "Postal Code", type text }}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{"Postal Code"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"CityCode"}),
    #"Added Custom Column" = Table.AddColumn(#"Replaced Value1", "Slicer City Postal Code", each Text.Combine({[CityCode], " - ", [Postal Code]}), type text) 
in
    #"Added Custom Column"

You may need to minor tweaks for your requirements .. hope this helps~

[Dunner2020]

@sevenhills , I do not want cross apply. You are right that there is no commonality between two tables. In practice, data operator enters the data in the fact table and manually selects the city and postal codes from two drop-down list.  So a fact table row contains meaningful information about Area and postal code. 

Is there any way of overcoming the issue?

[sevenhills]

Thanks, yes, two drop downs of city, postal code is common. other option is "city - postal code". 

I am trying to understand the final result you want in the slicer ... 

Do you want the data as like this?

let
    Source = Table.Combine({CityTable, #"PostalCodeTable"}),
    #"Changed Type" = Table.TransformColumnTypes(Source, { { "Postal Code", type text }}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{"Postal Code"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"CityCode"}),
    #"Added Custom Column" = Table.AddColumn(#"Replaced Value1", "Custom", each Text.Combine({[CityCode], [Postal Code]}), type text) 
in
    #"Added Custom Column"

shows as 

5730

724

...

AT

CK

... 

or 

something else in the slicer    ?

[sevenhills]

I lookedinto the file and looks like "City" sheet and "postal code" sheet do not have any commanality.

If you join, it will be come cross apply. Is it what you are expecting?

(Or) How do you know which postal code belongs to which city?

Thanks