Using Parameter to Select Zip Codes in Non-Mutually Exclusive Fields

[ExcelMonke]

Hello,

I am having some difficulty in returning a list of zipcodes from a table using a field parameter, and I am wondering if anyone here has any ideas on how to achieve this?

Goal: To show zipcodes on the map for the selected parameter
Dataset: This an example of what the data set looks like:

Zip Code	Market A	Market B	Market C	Market D
17275	City 1	Other	City 1	City 1
11834	City 2	City 2	City 2	Other
18350	City 3	Other	City 3	Other
13110	Other	City 4	City 4	Other
12818	Other	City 5	Other	City 5
16822	City 6	City 6	City 6	Other

Intended outcome: As you can see, zip codes are not mutually exclusive, meaning, one market can share the same zip code. What I want is that when selecting a market in a parameter set, to only show zip codes associated with a city, and exclude any row that contains "Other".

Current ideas tried: 
My parameter looks like this:

Market Selector = {
    ("Market A", NAMEOF('Market by Zip Code'[Market A]), 0),
    ("Market B", NAMEOF('Market by Zip Code'[Market B]), 1),
    ("Market C", NAMEOF('Market by Zip Code'[Market C]), 2),
    ("Market D", NAMEOF('Market by Zip Code'[Market D]), 3)
}

I have tried using a measure to only highlight zip codes selected, but it doesn't seem to work as intended:

Geography Map Filter = 
SWITCH(
    SELECTEDVALUE('Market Selector'[Market Selector Order]),
    0,1,
    1,1,
    2,1,
    3,1,
    0
)

To where the map would fill only where the measure returns 1. 

Any ideas are welcome! 

[Anonymous]

Hi @ExcelMonke,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[Anonymous]

Hi @ExcelMonke,

Thank you for reaching out in Microsoft Community Forum.

You can achieve this by creating a calculated table or a measure that dynamically returns only the relevant zip codes for the selected parameter value.

Please follow below steps to acheive this;

1.Make sure your Field Parameter is set up correctly (which it looks like you’ve done).

2.Create a new calculated column that dynamically evaluates the selected market field and filters out "Other".

Show Zip =
VAR SelectedMarket = SELECTEDVALUE('Market Selector'[Market Selector])
VAR SelectedCity =
SWITCH(
TRUE(),
SelectedMarket = "Market A", 'Market by Zip Code'[Market A],
SelectedMarket = "Market B", 'Market by Zip Code'[Market B],
SelectedMarket = "Market C", 'Market by Zip Code'[Market C],
SelectedMarket = "Market D", 'Market by Zip Code'[Market D]
)
RETURN
IF(SelectedCity <> "Other", 1, 0)

3.Use this measure as a filter on your Map visual:

-->Drag the Show Zip measure into the Filters pane for the visual.

-->Set the filter to is 1.

Please continue using Microsoft Community Forum.

If this post helped resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

[ExcelMonke]

Thanks for following up - unfortunately this did not work as a solution for me because the 
"Column [Market Selector] is part of a composite key, but not all columns of the composite key are included in the expression or its dependent expression"

I have never seen that error before, so not sure what to make of it

[Anonymous]

Hi @ExcelMonke,

Thank you for reaching out in Microsoft Community Forum.

The error message you encountered, "Column [Market Selector] is part of a composite key, but not all columns of the composite key are included in the expression or its dependent expression," indicates that the Market Selector column is involved in a composite key relationship with other columns in the model. This can happen when the column is used as part of a unique combination of columns to create a relationship between tables.

Please follow below steps to resolve the error;

1.Identify all columns in the composite key and ensure they are referenced correctly in your DAX expression or calculated column.

2.Adjust your DAX formula to include all necessary columns in the composite key. For example, include both Market and Zip Code in the expression, ensuring that the calculation is aware of all key columns.

3.Check that relationships between the Market Selector and other tables are correctly set up, ensuring that all relevant fields in the composite key are linked properly.

4.Simplify the DAX formula to test the basic functionality, then gradually add complexity to identify where the issue occurs.

Please continue using Microsoft Community Forum.

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

[ExcelMonke · Accepted solution]

Hi @Anonymous ,

circling back on this. Unfortunately your solution did not work for me but the following did (posting in case it could be helpful for others):

SWITCH(
    SELECTEDVALUE('Market Selector'[Market Selector Order]),
    0,IF(CONTAINSSTRING(VALUES('Market by Zip Code'[Market A]),"Other"),"",VALUES('Market by Zip Code'[Market A])),
    1,IF(CONTAINSSTRING(VALUES('Market by Zip Code'[Market B]),"Other"),"",VALUES('Market by Zip Code'[Market B])),
    2,IF(CONTAINSSTRING(VALUES('Market by Zip Code'[Market C]),"Other"),"",VALUES(''Market by Zip Code'[Market C])),
    3,IF(CONTAINSSTRING(VALUES('Market by Zip Code'[Market D]),"Other"),"",VALUES('Market by Zip Code'[Market D])),
""
)