cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StephenBrown
Regular Visitor

Filtering on a second datasource

Hi,

I've created an app to retrieve data from 2 Azure database tables. The first table contains the main information and the 2nd has data that i want to use in a graph. How can i use the ThisItem.field value to filter on the 2nd datasource?

Thanks

Stephen

1 ACCEPTED SOLUTION

Accepted Solutions

Hi again, I did a test and this works.  Please try this first and then we can substitute your data sources for the dummy data. This is all on the same screen

 

1) Create a gallery (Gallery1) with .Items = ["Small", "Medium", "Large"]

2) Insert a text box in the gallery with .Text = ThisItem.Value

3) Insert a text box to the right of the gallery and set .Text = Gallery1.Selected.Value

\\ This text box will not be used but it is to check the selected value is of the right type to use as a filter

4) Insert a pie chart

\\ We do it at this step so that the PieChartSample table is available at the next step

5) Insert a button above the gallery and set .OnSelect = ClearCollect(TestCollection, AddColumns(ShowColumns(PieChartSample, "City", "Population"), "CitySize", If(Population < 5, "Small", If(Population < 10, "Medium", "Large"))))

\\ I load the data in a collection so that we can check what is in there with the Content -> Collections menu

6) For the pie chart itself set .Items = Filter( TestCollection, CitySize=Gallery1.Selected.Value)

 

Then run the app, press the button to load the collection and select a size.  You should see something like the screenshot below.

If that is the case then you can substitute the gallery items with your first table and PieChartSample with your second table.  Let's see how that goes.

 

Capture_cities.JPG

View solution in original post

6 REPLIES 6
Steelman70
Power Participant
Power Participant

If I understood correctly, you want to select an item in Table1 and then filter Table2 with some value of the Table1 item.

 

Here is one possible approach:

1) Create a gallery, say Gallery1, with Table1 as the source

2) Create your graph with source as Filter(Table2, MyTable2Column = Gallery1.Selected.XXX)

Here the syntax of XXX will depend on the data that you need to select for.

Basically as soon as you select one of the items of the gallery, the source of your graph should update.

 

Alternativey you could use a drop down control for step 1.

 

Please let me know if you need more precise guidance.

Hi,

Thanks for the suggstion but it doesn't work.

When i add a chart to the same screen as the gallery and set the items filter to what you suggested i get a "The requested operation in invalid" message. If i add a textbox and set the filter to the same string then it populates with values correctly.

 

The full string i'm using for the chart item filter is: Filter('[dbo].[Budget_cost]', BCproject = Gallery1.Selected.project)

 

EDIT:

I copied data from my Azure tables into excel files and I can filter on the graphs. Is there a restriction (or am I doing something wrong) with multiple Azure database tables?

Hi again, I did a test and this works.  Please try this first and then we can substitute your data sources for the dummy data. This is all on the same screen

 

1) Create a gallery (Gallery1) with .Items = ["Small", "Medium", "Large"]

2) Insert a text box in the gallery with .Text = ThisItem.Value

3) Insert a text box to the right of the gallery and set .Text = Gallery1.Selected.Value

\\ This text box will not be used but it is to check the selected value is of the right type to use as a filter

4) Insert a pie chart

\\ We do it at this step so that the PieChartSample table is available at the next step

5) Insert a button above the gallery and set .OnSelect = ClearCollect(TestCollection, AddColumns(ShowColumns(PieChartSample, "City", "Population"), "CitySize", If(Population < 5, "Small", If(Population < 10, "Medium", "Large"))))

\\ I load the data in a collection so that we can check what is in there with the Content -> Collections menu

6) For the pie chart itself set .Items = Filter( TestCollection, CitySize=Gallery1.Selected.Value)

 

Then run the app, press the button to load the collection and select a size.  You should see something like the screenshot below.

If that is the case then you can substitute the gallery items with your first table and PieChartSample with your second table.  Let's see how that goes.

 

Capture_cities.JPG

PS Regarding Azure database tables, I have used multiple ones before and there was no issue.  Please be aware that at the moment only the first (or last) 256 rows of any Azure table will be returned.  Could the issue be that you are not retrieving all the rows that you would like to filter from the second table?

 

PPS Also, please try to select the columns of your second Azure table to create a source table for the pie chart as I did above: 1) your item name/ID etc. 2) your value 3) your filter column

Hi,

This worked and i guess that i have an issue with data in the Azure tables - that could be related to the 250 rows that you mentioned.

 

I have a work around for that, so all is good.

 

Thanks!

You are welcome.  Actually I did some tests yesterday and it seems that finally delegation is working as advertised, so that the 256 row limitiation can be worked around by using filtering and sorting:

https://powerapps.microsoft.com/en-us/tutorials/function-sort/#delegation

UPDATE: I have just done a test again and got back 500 rows from Access Web Apps, so should be the same for Azure now

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (2,595)