How do I get distinct values in a cascading filter?

[brianmccargar]

I am trying to create a cascading filter for locations, started at State / Provence, then City, and finally location name. My issue is I get multiple values for the same State / Provence. Is there any way to get the State / Provence values to just show up once?

[Adam Stout]

Is this in sys_choice or on some fact table?

[brianmccargar]

We are trying to use the Location [cmn_location] table directly.

[Adam Stout · Accepted solution]

Filters are designed to work on tables with a unique value.  To get that to work as-is, you would need a custom filter. Alternatively, you could use the hierarchy to get the locations that are states (city is empty and state is not).  Then get the locations that match, then go to the next level in the cascade.

I'm not sure of a simple solution here.

[brianmccargar]

Thanks for that answer Adam, that helps clear up my understanding of how filters work.

I agree it does not sound like a simple solution.