Filtering by Choice Columns in Dataverse and Power Apps. What are my Choices?

Well, isn’t that an interesting question? Choice or Choices (previously optionsets) are funny little things, because, although they appear as items in a list generally, they are actually rows of data in another table called String Maps. Each Choice option is stored in this table and they relate to the table they are added to. This means, Choice options are actually records, and that makes them a bit tricker to work with when filtering, especially for Choices (Multi Select).

We can do some simple filtering of a gallery:

Filter(Contacts, SingleSelect = Category.Business)

In the above, there is a column on the Contacts table called “SingleSelect” (That's the display name) and that is a choice column that is a global choice list sync’d with a choice called Category.

Then, using the dot notation, we move through the table to the row of data called Business. This is similar to using it to navigate through from a variable of a record to a column of that record e.g. varRecord.’Full Name’.

But I just said it was a table, so why is it acting like a record? That’s another confusing part of this. We could not use this notation in this way to get a row a data like: Contacts.’Fred Smith’.age so why are we doing it this way? Well again, it comes back to the table structure. As Terry Pratchett said, it’s turtles all the way down, this is tables all the way down. Inside the String Map table, it contains all of the choices, the tables they are connected to and the values. Thus although these are rows of data, they are sudo categorised as tables and then as records, meaning the Category.Business works to get that row of data.

Then we can filter a bit more dynamically. We have a Combo box with an list of Items that is Choices(Category) and allowing people to choose a record from that box. You can also use Choices(Contact.SingleSelect) as it still points to the same place.

Then to filter on that, we have:

Filter(Contacts, SingleSelect = ComboBox1.SelectedItems)

The combo box has the records as options the user can choose, so when we compare it to the column on the record, the types match therefore it renders results. What this means is that you cannot filter based on the name of the choice though, because the source column, in this case SingleSelect is like a lookup column and not a text filter we can just add. You will get errors about incompatible types if you try.

What if we do want to filter by it’s text value though? We can do this, but what we need to do is get the record instead of the text value.

If(TextInput1.Text = “Business”, Set(varCategory, Category.Business))

The above checks the Text Input of what the user has typed and if it equals “Business”, set a variable which is the record of Category.Business. You can then use this in a filter:

Filter(Contacts, SingleSelect = varCategory)

OK, that’s all well and good, but what if we have a Choices column and not a Choice column? Where we can select multiple choices from the list. This works in the same way, we still need the records of those choices to filter. The easiest way to do this is to add to a collection.

ClearCollect(colCategory, Category.Action, Category.Business, Category.Other)

Now we have 3 records in our collection and we can add them into the filter:

Filter(Contacts, colCategory in MultiSelect)

MultiSelect is a Choices column allowing for multiple values and the colCategory is the collection we make in the previous step. At this point, you may get a delegation warning about the data, but I’m just going to move past this for now.

Let’s go back to our other example, where we may have some text values for our Choices column and we need to get the records. We’ll put the multiple values in a collection first:

ClearCollect(colCategoryText, “Business”, “Family”)

Then we need to create a new collection of the records where the text equals this in the choice column. We’ll need to loop over the records in the collection and switch based on the values (no big nested IF statements here), and gather the records:

ClearCollect(ColCategory,
    ForAlls(colCategoryText,
            Switch(Value,
            "Business", Category.Business, 
            "Family", Category.Family,
            "Other", Category.Other)
        )
)

In the above example, the “Value” is the column name in the collection colCategoryText, so the switch statement is finding it’s text value and running through. The ForAll, loops through each record, “Business” first and then “Family” and creates two rows in the collection, which are both records.

You’ll notice the difference in the collections. The one where we collect text, shows the text, the ones where we collect the choice records, doesn’t render, but we can see them in there.

Hope this helps demystify the elusive Choice and Choices columns in Dataverse and Canvas Apps.

Ciao for now!