I'm working with a data table that's populated by an Excel table titled "Report Card". Within the document, there are two specific columns I need to use to filter the data. The first column, "Service" is not selected for the table. I want to use FILTER to show only the records with "ABC" in the "Service" column. Within that same table is a column titled "Section", which is selected viewable and attached to a dropdown control. The "Section" column is a choice column but I only want to see the choices that are related to "ABC" in the "Service" column. I also need to add an option for "ALL" to the dropdown control so that when user navigates to this page, they see only items that fall into the "ABC" category but can select either one individual choice or all choices from the "Section" column.
This formula works to filter for "ABC": Items = 'Report Card', Service.Value = "ABC".
This formula works to add "ALL" to the dropdown control:
If('Sections Dropdown'.Selected.Value="ALL",'Report Card',Filter('Report Card',Section.Value='Sections Dropdown'.Selected.Value))
This is what I used to combine the two formulas:
If('Sections Dropdown'.Selected.Value="ALL",'Report Card',(Filter('Report Card',Section.Value='Sections Dropdown'.Selected.Value && Service.Value = "ABC")))
This last formula works in that it doesn't give me any errors, however, it also doesn't filter the dropdown control choices to only include choices related to "ABC".