cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
enlitzinger
Helper II
Helper II

2000+ cloud values but can't get a filter to work

I'm currently using Powerapps Visual within Power BI via a PowerBIIntegration.data connection.  I know PowerApps limits values to 2000 when connected to a cloud database, which is fine if I can accomplish what I'm thinking I need to do.  

 

My data source has 2000+ values (~4200) for the 'Title' column, which is comprised of 3 distinct values (Legal, Product Compliance and Regulatory Compliance).  If I apply the filter by the PBI filter format (far right in attached screen shot) the data is under 2000 values and it works great - which is what I need - the user will only look at data/values aligned to 1 of these 3 'Titles' at a given time.  I attempted both a gallery filter and drop down selection filter within the PowerApp itself, but the app still pulls in the ~4200 values associated with 'Title', thus returning the 'too many values error'.  Selecting any 1 of the Titles values doesn't prevent the app from still pulling in all ~4200 values. 

 

My question is - can I, and if so how do I, apply a filter within PowerApps (whether its a  PBI filter within the app (not on the side or accessed via the filter icon) as seen on the screen grab or a custom formula within a Gallery or dropdown) for the app to only pull in 1 'Title' category at a time?  

 

Within the app itself the formulas are:

 

My Dropdown Items formula is: 

Distinct(PowerBIIntegration.Data,'Title')

 

My Gallery Items formula to return the filtered results is: 

Distinct(Filter(PowerBIIntegration.Data,'Title' = Dropdown1.Selected.Result && 'Line(s) of_x00' in Split(LOB.Text,", ") && 'Product(s)' in Split(Prod.Text,", ") && 'Target Audience' in Split(Audience.Text,", ").Result),Reviewer)

 

My other option was a Gallery Filter to filter 'Title':

Distinct(Filter(PowerBIIntegration.Data, 'Line(s) of_x00' in Split(LOB.Text,", ") && 'Product(s)' in Split(Prod.Text,", ") && 'Target Audience' in Split(Audience.Text,", ").Result),'Title')

 

***for background info...the Split(ABC.Text,", ") &&......are gallery filters that are returning rows where the app user can select multiple options across the 3 values (LOB, Products, Audience).

3 REPLIES 3
v-xida-msft
Community Support
Community Support

Hi @enlitzinger ,

Could you please share a bit more about your scenario?

Do you want to filter your Gallery items based on single one Title category?

 

Based on the issue that you mentioned, I think this issue is related to PowerBIIntegration.Data formula in your embedded canvas app. The PowerBIIntegration.Data formula would return proper results from your Power BI report, if you do not set a filter in your Power BI report, the PowerBIIntegration.Data formula would return all records from your Dataset used in Power BI report.

Note: Currently, there is an known limit within PowerBIIntegration.Data formula -- it could only load 1000 records at most from Power BI report into the embedded canvas app through PowerApps Custom Visual.

Please check the following thread about this limit:

https://powerusers.microsoft.com/t5/Building-Power-Apps/PowerBIIntegration-record-limit-1000/td-p/28...

 

Based on the formula that you mentioned, I could not find any error syntax with it. Also please consider take a try with the following workaround:

Firstly, please add a ComboBox control in your embedded app instead of Dropdown control, set the Items property to following:

Distinct([@PowerBIIntegration].Data, Title)

set the SelecteMultiple property of this ComboBox to false.

Then within your Gallery, set the Items property to following:

Distinct(
          Filter(
                 [@PowerBIIntegration].Data,
                 If(
                    IsBlank(ComboBox1.Selected.Result),
                    true,
                    Title = ComboBox1.Selected.Result         // Title column should not be wrapped with single quote
                 ),
                 'Line(s) of_x00' in Split(LOB.Text,", "),
                 'Product(s)' in Split(Prod.Text,", "), 
                 'Target Audience' in Split(Audience.Text,", ").Result
         ),
         Reviewer
)

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Using the solution in the linked thread provided I created collections to parse out each 'batch' of 2000, then created a new collection to join the independent batch collections (formula below) on the 'on select' property for a button.  However, when I look at each of my individual collection it seems to return the same data from the first collection (rows 1-2000).  Collection 2 should return rows 2000-4000 based on the accepted solution in the provided thread.  Collection 3 should return rows 4000-4200.  However, all the collections return rows 1-2000.

 

It should be:

ds1 = 1-2000

ds2 = 2000-4000

ds3 = 4000-4200

dsjoined = 1-4200 (this becomes my datasource of my gallery filters)  

 

Collect(ds1[@PowerBIIntegration].Data);Collect(ds2,FirstN(Sort([@PowerBIIntegration].Data,Descending),2000));Collect(ds3,FirstN(Sort([@PowerBIIntegration].Data,Descending),200));Collect(dsjoined,ds1,ds2,ds3)

 

What am I missing?   Why are all of the collections only returning 1-2000?

Hi @enlitzinger ,

Currently, the [@PowerBIIntegration].Data is not a Delegable data source in canvas app, so you could only handle first 2000 records at most locally in your app.

Note: If you use a Non-delegable data source in your canvas app, it would load first 2000 records into your canvas app memory firstly, then when you apply function to this data source, it just apply the function to these cached records within your app memory, rather than retrieve data from your Power BI Dataset.

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 (2,124)