Filters - Choice

[chris_young]

All, 

I have a screen which has many filters on it. Below is the code I'm using and a screenshot of the error and screen. 

I cannot get the SharePoint Choices (module) to work. I've read some blogs about using Distinct and Choice and some other things but I can't get it. All the other filters work, but they aren't Choices. Any ideas?

Filter(
'Customer Reference Data',
CompanyDropDown.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
IndustryDropDown.SelectedText.Value=Blank() || Industry.Value =IndustryDropDown.SelectedText.Value,
IndustrySubgroupDropDown.SelectedText.Value=Blank() || 'Industry Subgroup' =IndustrySubgroupDropDown.SelectedText.Value,
ModulesDropDown.Selected.Value=Blank() || Module = ModulesDropDown.Selected.Value,
//Referenceable.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
//SolutionDropDown.SelectedText.Value=Blank() || Solutions.Value = SolutionDropDown.SelectedText.Value,
EngagementTypeDropDown.SelectedText.Value=Blank() || Engagement.Value =EngagementTypeDropDown.SelectedText.Value)

[v-qiaqi-msft]

Hi @chris_young，

Based on the issue that you mentioned, do you want to filter based on the Module(Choice) column?

Could you please tell me that how you set the Items property of the ModulesDropDown, using Choices() or anything else?(IMPORTANT)

Generally, we use choices() to set a Choice Dropdown Items property as below:

Choices(Product.Module)

So, if you set your ModulesDropDown as above, please modify your formula as below:

Filter(
'Customer Reference Data',
CompanyDropDown.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
IndustryDropDown.SelectedText.Value=Blank() || Industry.Value =IndustryDropDown.SelectedText.Value,
IndustrySubgroupDropDown.SelectedText.Value=Blank() || 'Industry Subgroup' =IndustrySubgroupDropDown.SelectedText.Value,
ModulesDropDown.Selected.Value=Blank() || Module.Value = ModulesDropDown.Selected.Value,
//Referenceable.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
//SolutionDropDown.SelectedText.Value=Blank() || Solutions.Value = SolutionDropDown.SelectedText.Value,
EngagementTypeDropDown.SelectedText.Value=Blank() || Engagement.Value =EngagementTypeDropDown.SelectedText.Value) 

Note that you should refer to Module column using "Module.Value"

[WarrenBelz]

Hi @chris_young ,

Is Module a Choice field? If you you need

Module.Value = ModulesDropDown.Selected.Value

Also SelectedText is deprecated - use Selected instead.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[chris_young]

The items on the Module dropdown is set as:

SortByColumns(Choices('Customer Reference Data'.Module),"Value",Ascending)

In the gallery, if I add the .Value on the end I am still getting the same error message in the gallery

[chris_young]

Thank you Warren. Yes Module is a choice field in SharePoint. 

I tried to add the .Value on the end but I'm still getting the error. I will change the selectedtext to just selected. Thank you for that piece of information. 

[chris_young]

@WarrenBelz @v-qiaqi-msft Sorry huge point I just realized I left out. The Choices is a MultiSelect in Sharepoint. They will only filter on one of them, but the backend allows for multiple selections in that field. 

[v-qiaqi-msft]

Hi @chris_young，

If the Choice field in your SP list allows multi select, either you could change the select setting from multi-select to single-select, or you could simply change the formula based on the multiple selections. Of course all changes have to be based on your actual needs, if you really only need single selection, then changing the data type is the best option.

1). If you just need a single selection, you could go to column setting and turn off the allow multi select option, and please modify as below:

Filter(
'Customer Reference Data',
CompanyDropDown.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
IndustryDropDown.SelectedText.Value=Blank() || Industry.Value =IndustryDropDown.SelectedText.Value,
IndustrySubgroupDropDown.SelectedText.Value=Blank() || 'Industry Subgroup' =IndustrySubgroupDropDown.SelectedText.Value,
ModulesDropDown.Selected.Value=Blank() || Module.Value = ModulesDropDown.Selected.Value,
//Referenceable.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
//SolutionDropDown.SelectedText.Value=Blank() || Solutions.Value = SolutionDropDown.SelectedText.Value,
EngagementTypeDropDown.SelectedText.Value=Blank() || Engagement.Value =EngagementTypeDropDown.SelectedText.Value)

2). If you need a multi selection, please modify as below:

Filter(
'Customer Reference Data',
CompanyDropDown.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
IndustryDropDown.SelectedText.Value=Blank() || Industry.Value =IndustryDropDown.SelectedText.Value,
IndustrySubgroupDropDown.SelectedText.Value=Blank() || 'Industry Subgroup' =IndustrySubgroupDropDown.SelectedText.Value,
If(
IsEmpty(ModulesDropDown.SelectedItems.Value),
IsEmpty(Module.Value)
) || Concat(
ModulesDropDown.SelectedItems.Value,
Value,
","
) in Module.Value,
//Referenceable.SelectedText.Value=Blank() || Title =CompanyDropDown.SelectedText.Value,
//SolutionDropDown.SelectedText.Value=Blank() || Solutions.Value = SolutionDropDown.SelectedText.Value,
EngagementTypeDropDown.SelectedText.Value=Blank() || Engagement.Value =EngagementTypeDropDown.SelectedText.Value) 

[chris_young]

@v-qiaqi-msft Thank you, that got me halfway there. I'm getting an error on the Concat that has invalid arguments but I will work through that. The SharePoint list needs to be a multi-select, but the filter will only filter on a single item within that multi-select. I think that is where I'm getting hung up. If there are A, B, and C, assigned to that record, the end-user will filter on an individual dropdown basis. They would select A and anything with an A should then filter in the Gallery. Even if the order is B,C,A or A,B,C