Trouble with Distinct formula

[Timmo1016]

Hello!

I am pretty new to power apps. I am trying to get a distinct list from a connected excel file, and i am having trouble. My data is structured as follows:

Dashboard Name           Metric Name   

Dashboard 1                     Metric 1 

Dashboard 1                     Metric 2

Dashboard 1                     Metric 3

Dashboard 2                     Metric 4

Dashboard 2                     Metric 5

Dashboard 2                     Metric 6

and so on..

I am trying to bring the table into a gallery, and show me a unique list of Dashboard Names, where I want to be able to click into to see what metrics are associated with that dashboard.

I have tried to change the 'items' section for the gallery to the following:

Distinct(Table1, 'Dashboard Name') but am getting an error.

is my only option to create another datasource with a distinct list of dashboard names, and relate the metrics to that? I should mention the raw datasource is ~14000 rows with other data than just 'metric name' I would like to be able to access.               

[Amik]

@Timmo1016 - could you confirm what the error message is?

The Distinct function is not delegable. This means the function will only run on the first 500 rows in your Excel file (although this can be increased to a maximum of 2000).

[Timmo1016]

I don't see any error text, i just get a red X over the gallery and all the data disappears.

I think the issue is I am trying to call ThisItem.'Dashboard Name' on a Label inside the Gallery. If I change that to ThisItem.Value, then it seems to work. However, like you said it doesn't catch all the data because there are so many rows, and then I don't have any of the other information available to me (my only options are 'Value' and 'IsSelected'). 

Can power apps handle multiple data sources? That is, can I connect to another excel sheet that is just a distinct list of names to use for the Gallery? Then when the name is clicked on, go to another gallery that shows the metrics related from the other data source? 

[timl]

@Timmo1016 

>> Can power apps handle multiple data sources? That is, can I connect to another excel sheet that is just a distinct list of names to use for the Gallery? Then when the name is clicked on, go to another gallery that shows the metrics related from the other data source? 

In answer to this, yes, it's possible to add multiple data sources to an app. In this example, if you were to add a gallery of distinct Dashboard values to a gallery called galleryDistinct, you would set the Items property of your metrics gallery to:

Filter(YourMetricsTable, DashboardName = galleryDistinct.SelectedItem.DashboardName)

You're correct that due to the size of you Dashboard list, the number of rows exceeds the delegation limit so it therefore doesn't show accurate results.

[Timmo1016]

@timl 

Thank you! 

I tried that and the Metrics Gallery is blank. this is what I have

Table1

Workbook Name           Metric Name   

Dashboard 1                     Metric 1 

Dashboard 1                     Metric 2

Dashboard 1                     Metric 3

Dashboard 2                     Metric 4

Dashboard 2                     Metric 5

Dashboard 2                     Metric 6

Table1_1

Workbook Name

Dashboard 1

Dashboard 2

Dashboard 3

Dashboard 4

and so on..

I created a gallery with Table1_1 (the unique list of dashboards) named Gallery1.

I then created a second gallery called Gallery2 and changed the items property to:

Filter(Table1, 'Workbook Name' = Gallery1.Selected.'Workbook Name')

I then changed the Title in Gallery2 to:

ThisItem.'Metric Name'

And it just shows blank.

any insight would be appreciated 🙂 Thanks Again!