Forum Discussion

ExcelMonke's avatar
ExcelMonke
Impactful Individual
1 year ago
Solved

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

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 zi...
Need Help
  • ExcelMonke's avatar
    ExcelMonke
    1 year ago

    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])),
""
)
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    ExcelMonke
    Impactful Individual
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        ExcelMonke
        Impactful Individual
        1 year ago

        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])),
""
)