If you want to show the distinct column values from a a table, it's very important to appreciate that from Power Apps, the Distinct function is not delegable. This means that it may not return all the values that we expect, and it is a problem that often catches out app builders. This post examines this behaviour in more detail.
Recreating the problemTo recreate the problem, let's create an app and set the row count limit to 10. Whenever we test an app, a great tip is to set the row count limit to a low value. This makes it more obvious if an app doesn't return the expected records.
We'll attempt to show the distinct country values form a table called Property. From SQL Server, we see that there are 207 distinct values.
The Distinct function is not delegable
From Power Apps, we call the Distinct function to return the distinct countries.
The confusing thing here is that formula bar does not underline the formula in blue, or display any delegation warning messages. This can easily fool us into thinking that the formula is delegable and should return all expected values. As the screenshot beneath highlight, the output of this formula returns 10 rows only.
When we profile the call to SQL Server, we see that Power Apps attempts to
resolve the distinct function by selecting the top 10 records only from the
property table. From this, we can conclude that the distinct function is
How to correctly return distinct values from a table
The correct way to return distinct values in a delegable way is to build a view, which is covered in my post here.
We define a view in SQL Server that returns the distinct countries and connect to this view from our app.
CREATE VIEW [dbo].[vw_DistinctCountries]
SELECT DISTINCT Country
The view will be completely delegable, and will return the expected results.
App builders can be fooled into thinking that the Distinct function is delegable due to a lack of warning messages. It's important to appreciate that the Distinct function is not delegable, and that the best way to return distinct values from a SQL Server data sources is to use a view.