[SOLVED] Count Rows with by filtering result of combo box and Choice Column in Sharepoint

Count Rows with by filtering result of combo box and Choice Column in Sharepoint

I am trying to count number of rows which added by the users, and the value of "EntityNameCombo" match with the value of 'Entity Name' in Sharepoint 'Data Collection'. While EntityNameCombo is a combo box and the value is Choices([@'Data Collection'].'Entity Name'), and 'Entity Name' is a Choice column in Share point.

This is the code I am trying:

CountRows(Filter('Data Collection','Modified By'.Email=User().Email &&
'Entity Name'.Value = EntityNameCombo.SelectedItems))

However, it is not working with error: Incompatible types for comparison. These types can't be compared: Table, Table.

Your help is much appreciated.

Solution

As you could see from the error message, table values cannot be compared directly. The SelectedItems property of the combo box is a table with all values that have been selected, and the 'Entity Name' property of each record in SharePoint is also a table that contains the choices for the record.

We can solve this by converting the table into something that can be compared. For example, by concatenating the values from the choices, similar to the expression below:

CountRows(
    Filter(
        'Data Collection',
        'Modified By'.Email=User().Email &&
            Concat(Sort('Entity Name', Value), Value, ",") = Concat(Sort(EntityNameCombo.SelectedItems, Value), Value, ",")
    )
)

There are other alternatives, but I think this is the simplest one. Notice that the order of the choices from SharePoint or from the combo box are not guaranteed to be sorted, which is why the expression is first sorting the values prior to concatenating them.