Unanswered

Filter Not Delegating From PowerBIIntegration

(0) Share

Report

Posted on by Ajh

183

I'm trying to use the powerBIIntegration as input to filter a sql data source. Here is what I'm using which I think is close but I'm missing something syntax wise. Any help would be greatly appreciated. The only field I'm bringing in from powerBI is the 'id' column from the same table I'm doing the look up on. End result of the app is going to be to make some edits to the data in that table with a patch function.

Max(
 Filter('UDM.StormData_2_T_1',
 id in [@PowerBIIntegration].Data.id
 //&& !IsBlank(Class)
 )
 ,Value(Class)
)

I get a delegation warning and it isn't returning any records, even though there should only be 4 records returned with this. When I highlight [@PowerBIIntegration].Data.id with my mouse I see the four IDs that I am looking for, but it doesn't appear to be sending these back to the sql database to look them up.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@Ajh

The 'in' operator maybe your issue here?

From the docs:

The 'in' (substring) operator. Supported for ("string value" in <column>), but not for (<column> in "string value").

Maybe if your id can be a string value it'll work. The other issue maybe that the PBI Integration may have delegation issues, not sure, but that may be worthwhile researching?

Was this reply helpful? Yes No
Ajh 183 on at

Like
a
(0)

Report
Copy link

Link copied!
I think you were correct, in was definitely part of the problem. This is what I'm currently using but I hate the idea that it is looping through the PowerBIIntegration data set and potentially making multiple calls to the sql server when it should all be doable in one call. I'm not sure if it's behaving that way, I'm just afraid it is because that's definitely the way the code is written 😕

Clear(StormHere); ForAll(PowerBIIntegration.Data As Test, Collect(StormHere, Filter('UDM.StormData_2_T', id = Test.id) ) );

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!

@Ajh

Yes, you are correct. ForAll will send records 1 by 1. It is best suited to 1-2 fields in a few records that need updating.

A better way is to use Patch(), ie

Patch( Table1, Table2)

However, when using this method the schemas of the tables must match otherwise PowerApps will throw an error. This @RezaDorrani video explains this is exceptional detail.

https://www.youtube.com/watch?v=VzrarcM9W5k

So long as the schemas of both tables are setup correctly, this method will greatly speed up your data updates.

Was this reply helpful? Yes No
Ajh 183 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes hopefully I can do the patch when it comes to the update, but even before doing the update it sucks to have to do this loop just to get the original records. Surely there's a way to make one call and get all the elements with an ID in my list. 😕

Was this reply helpful? Yes No