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.

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