cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gjakova
Advocate II
Advocate II

Filtering from multiple dropdowns (using multiple tables from the database)

Hi there,

I've been struggling with the following thing (I thought it would be super easy...but nope):

I am using a SQL database and have multiple tables. In one of my screen I have 5+ filters and 3 filters are from 1 table and the two other tables are from different tables. For example
Filter 1 = ContractID from the table "Contracts"
Filter 2 = ContractType from the table "Contracts"
Filter 3 = Country from the table "Countries"
Filter 4 = Currency from the table "Currencies"

All my tables are related to my "Contracts" table, thus Contracts being the fact table and the other ones being the dimension tables.

How can I filter a datacard in PowerApps based on multiple filters from different tables within the SQL database?

In Power BI it is super easy, just add your filters and drag the values from the tables in it and DONE, but in PowerApps this seems to be a nightmare...?

I watched a video from Shane Young https://www.youtube.com/watch?v=kLGglidmPxg and reading the comments it seems to be a chaos if you try to use more than 2 filters.... what the hell, seriously?

 

Hope someone could give me a hand, thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
LRVinNC
Super User
Super User

Look at using Collect to save the results in a collection and Add Columns to append additional data from the second table via a Lookup.

 

Collect:  https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-clear-collect-clearc...

AddColumns: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping (there is a very good example in here of the two used together)

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

5 REPLIES 5
LRVinNC
Super User
Super User

I have apps with banks of as many as 10 filters that work just fine together with lightning fast results.  However, when I am using multiple data sources, I have always had to store the combined data in a collection when I initially load the data, and then I can filter against the consolidated collection as a single data source.

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi @LRVinNC that's nice to hear. Do you mean different tables? Because I am using one data source which is SQL Server (1 database), but the database has multiple tables.

Do you have an example of such a code perhaps?

LRVinNC
Super User
Super User

That's what I mean by multiple data sources.  Each table IS a separate data source.  They just happen to use the same SQL connection.

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Thank you, I will look into on how to store the data into a combined collection.

LRVinNC
Super User
Super User

Look at using Collect to save the results in a collection and Add Columns to append additional data from the second table via a Lookup.

 

Collect:  https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-clear-collect-clearc...

AddColumns: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping (there is a very good example in here of the two used together)

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,628)