How can I add an "All" option to a dropdown?

[dlprentice]

I've got a data table, and a few dropdowns that let me filter this data table. I'll like to be able to have an "All" option on each of the 3 dropdowns in question to let the end user broaden their search. Is this possible given the code snippets below?

Side note: If StateDropdown is set to "All" CityDropdown would need to be forced to set to "All". Once you view the code snippets you will see CityDropdown is dependent on the state selected in the StateDropdown.

Data Table (Items):

Filter (
    AddColumns(
        'ESN Ticket Tracker',
        "City",
        LookUp(
            'Networking Device List Master',
            'DNS Entity Name' = 'ESN Ticket Tracker'[@'Service ID'],
            City
        ),
        "State",
        LookUp(
            'Networking Device List Master',
            'DNS Entity Name' = 'ESN Ticket Tracker'[@'Service ID'],
            State
        )
    ),
    Status = StatusDropdown.SelectedText.Value,
    State = StateDropdown.SelectedText.Value,
    City = CityDropdown.SelectedText.Value
)

StatusDropdown (Items)

Sort(Distinct('ESN Ticket Tracker',Status),Result)

StateDropdown (Items)

Sort(Distinct('Networking Device List Master', State),Result)

CityDropdown (Items)

Sort(Distinct(Filter('Networking Device List Master', State=StateDropdown.SelectedText.Value),City),Result)

[JR-BejeweledOne]

Unless 'All' is an option in the data sources that populate your dropdowns, you will have to do it a different way.

I just did something similar last week in an app I am working on.    What I did was to add a checkbox under my dropdown with the option to select 'All'.

In your case you could have 2 checkboxes.   Set the default property of the 2nd checkbox to be selected if the 1st one is selected and then modify your filtering to include if those 2 boxes are selected.

[dlprentice]

I understand the idea, would you mind giving a coded example?

[dlprentice]

I think I'm getting closer,

I made a Check box called AllStates and set its default value to this:

true

I made a Check box called AllCities and set its default to this:

If(AllStates.Value=true, true)

 Since I don't have "All" as an option I suppose I'd need to use an If() statement somehow and integrate it into the main "Items" I have:

Filter (
    AddColumns(
        'ESN Ticket Tracker',
        "City",
        LookUp(
            'Networking Device List Master',
            'DNS Entity Name' = 'ESN Ticket Tracker'[@'Service ID'],
            City
        ),
        "State",
        LookUp(
            'Networking Device List Master',
            'DNS Entity Name' = 'ESN Ticket Tracker'[@'Service ID'],
            State
        )
    ),
    Status = StatusDropdown.SelectedText.Value,
    State = StateDropdown.SelectedText.Value,
    City = CityDropdown.SelectedText.Value
)

I'm currently trying to research how to properly nest this in, but if you see something obvious please let me know!

[JR-BejeweledOne]

That is exactly what you need to do.    In your If statement, you need to add a filter statement to match your 'All' conditions:   Since you are forcing the Cities to "all' based on the first checkbox, you may be able to get away with just using that one for comparison.    Here is a very basic guide.

If(
    citycheckbox = "All",  some filter value,
    Original filter value

)

[dlprentice]

I think I might have this nested wrong, could you see if you spot anything? It seems to be working but in the wrong order.

I seem to only be able to select a specific city and a specific state when AllStates is checked, and AllCities is unchecked. I would only expect this to happen only if both were false.

Filter (
    AddColumns(
        'ESN Ticket Tracker',
        "City",
        LookUp(
            'Networking Device List Master',
            'DNS Entity Name' = 'ESN Ticket Tracker'[@'Service ID'],
            City
        ),
        "State",
        LookUp(
            'Networking Device List Master',
            'DNS Entity Name' = 'ESN Ticket Tracker'[@'Service ID'],
            State
        )
    ),
    Status = StatusDropdown.SelectedText.Value,
    If(
        AllStates.Value = false,
        State = StateDropdown.SelectedText.Value,
        State = State,
        If(
            AllCities.Value = false,
            City = CityDropdown.SelectedText.Value,
            City = City
        )
    )
)

 StateDropdown (Items):

Sort(Distinct('Networking Device List Master',State),Result)

AllStates (Default) Checkbox:

true

CityDropdown (Items):

Sort(Distinct(Filter('Networking Device List Master', State=StateDropdown.SelectedText.Value),City),Result)

AllCities (Default) Checkbox:

If(AllStates.Value=true, true)

[dlprentice]

Screenshot for clarity.

[dlprentice]

@eka24 Hey! Would you mind helping me out with this one too? 🙂

[JR-BejeweledOne]

Your If statement only evaluates the city if the State is False. is this what you intended?

[dlprentice]

It is not, 

I was going for:

If box is checked, use the value in the dropdown, otherwise use the default values (I think State = State does that for me).

Same with city, figured nesting if() statement would work.