Filter delegating to Dataverse, nested Filter not delegating.

(1) Share

Report

Posted on by DF-28111035-0

Hi, my problem is that when I used the Filter function in Power Apps each section on its own works fine in combination with [@PowerBIIntegration].Data when speaking to a Dataverse table with 81288 records. When I use nested filters however the result appears to be acting as if it is not delegating.

To elaborate, the data that is viewed in an embedded Power App in Power BI should only show records where comments are not null. For this I use the following coding:

Filter(DVMIBCs, Not (DemandChangeValidation = Blank()))

This line of code works perfectly fine returning the 18 rows where comments are not null.

If I run this code:

Filter(DVMIBCs, Brand in [@PowerBIIntegration].Data.Brand)

It searches through the 127 brands and also delegate ok where I can go through all 81288 records in the gallery.

If however I nest functions using:

Filter(

Filter(DVMIBCs, Not (DemandChangeValidation = Blank())),

Brand in [@PowerBIIntegration].Data.Brand)

Filter(DVMIBCs, Not (DemandChangeValidation = Blank()), Brand in [@PowerBIIntegration].Data.Brand)

I only get two records back.

The above is based on no records being selected in the Power BI drop down for Brand. If I do select one or two brands then that is passed from Power BI through PowerBIIntegration.Data to the Power App that then successfully passes through to Dataverse and returns the few records that have comments for the selected brand.

What confuses me is that yes there are 127 brands but the flow is to process the filtered dataset where there are blanks and that subset dataset is then passed to the outer filter that checks to see if there are any selected brands in the 18 rows from the inner Filter function.

Any help would be greatly appreciated, thank you in advance.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

jpespena 335 on at

Like (0)

Report
Hi,

Try this:

With( { varItems: Filter( DVMIBCs, Brand in [@PowerBIIntegration].Data.Brand ) }, Filter( varItems, Not(DemandChangeValidation = Blank()) ) )

If my post helped resolve your issue, please click Accept as Solution—this helps others find it more easily and marks the item as closed. If you found this or my previous reply helpful, a Like would also be appreciated!

Was this reply helpful? Yes No
DF-28111035-0 4 on at

Like (0)

Report

Thank you for the response. A powerapps SQL CTE equivalent is something I did try. Using your code I set my delegation to 1 to better test delegation.

I then took your code and replaced varItems with the Dataverse source name DVMIBCs

Filter(

DVMIBCs,

Not(DemandChangeValidation = Blank())

)

This is what came back

The number 11 in the Gallery comes from the code ViewGallery.AllItemsCount

If I then take the other part of your code for brands:

Filter(

DVMIBCs,

Brand in [@PowerBIIntegration].Data.Brand

)

I get this back:

Although I set my delegation limit to 1 I took the count down to 2200 just to cover the 2000 max limit that can be used.

If I input your code:

With(

{

varItems:

Filter(

DVMIBCs,

Brand in [@PowerBIIntegration].Data.Brand

)

},

Filter(

varItems,

Not(DemandChangeValidation = Blank())

)

)

I get nothing returned. Again I also tried:

Filter(
Filter(DVMIBCs, Not (DemandChangeValidation = Blank())),
Brand in [@PowerBIIntegration].Data.Brand)

and

Filter(DVMIBCs, Not (DemandChangeValidation = Blank()), Brand in [@PowerBIIntegration].Data.Brand)

Any further help greatly appreciated

Was this reply helpful? Yes No