I've got an App that I'm trying to speed up by using security filters to reduce the number of synced rows. To check that this is working I'm using various USERSETTINGS columns to do a simple:
COUNT(Table[KeyColumn]
Most are fine but one security filter for the Site table always returns all rows. Which is PITA because that tables has got 26,000+ rows 😭. Here the security filter is set to use:
IN([Team],Select(Staff[Locations],[Login Email]=UserEmail()))
Site[Team] is an ENUM, Staff[Locations] is an ENUMLIST. Using a USERSETTINGS column I can see that Select(Staff[Locations],[Login Email]=UserEmail())) results in "London". Yet if the filter was working this should return 4000ish rows, but its pulling in 26000+.
I've tried the following for the security filter:
- IN([Team],ANY(Select(Staff[Locations],[Login Email]=UserEmail()))
- IN([Team],LIST(Select(Staff[Locations],[Login Email]=UserEmail())))
- IN([Team],LIST(SPLIT(Select(Staff[Locations],[Login Email]=UserEmail()),",")))
But all the above returns all the rows. The only way I got it to reduce was to user [Team]="London". Then it shows 4000ish but this is obviously not usable.
Anyone any ideas of what I'm getting wrong here? Would CONTAINS() be better as all Site[Team] choices are quite distinct? Does it make any difference that I'm also using "Preview App as:", not sure that it does as other security filters seem to work fine.
Simon, 1minManager.com
LinkedIn
Twitter
