Help with optimizing gallery view formula, to avoid black screen when changing filter.

[MegaOctane1]

I have 3 sharepoint lists. 

List 1 called "Budsjett"

It has Account, CostCenter and year column and 12 months column

GL_Account (lookup column from table  GL_Accounts)	CostCenter (lookup column from table CC)	Year	January	February	March
73305000	40001134	2024	1000	2000	3000
73608900	40001137	2024	500	1000	1500
73608900	40001138	2025	200	300	400
73608900	40001138	2024	200	600	900

List 2: List of Cost Centers called "CC"

CostCenter	CostCenterName	Ansvarlig(person column)
40001140	Purchasing	mary@here.com
40001134	Marketing	mary@here.com
40001137	Export	john@here.com
40001138	Commercial	john@here.com

List 3: List of Accounts, called "GL_Accounts"

GLAccount	GLName	GLType
73305000	Supplies	Other Cost
73608900	Office Supplies	Other Cost
14569870	Salary	Staff Cost
14569871	Other Staff Cost	Staff Cost

In my report I also have a filter on cost center so that the user can filter the entire list on certain cost centers that have themselves as responsible. 

So i want to get items that has john@here.com as responsible person, and the account number is of type "other cost" and then look at the dropdown filter called "FilterCC_2" and give me this value, or if nothing is selected in filterCC_2, show me all.

Here is my code. It works, but gives delegation warning...

With(
    {
        MyDisplayList: Filter(
            Budsjett;
            Year = varYear; 
            CostCenter.Value in Filter(
                CC;
                Ansvarlig.Email = varUser.myProfile.mail
            ).CostCenter && (IsBlank(FilterCC_2.Selected.Value) || CostCenter.Value = FilterCC_2.Selected.Value); 
            GL_Account.Value in Filter(
                'GL Accounts';
                GLType = "Other Cost"
            ).GLAccount; varReset 
        )
    };
    Sort(MyDisplayList;Endret;SortOrder.Descending)
)

What i should end up with is:

GL_Account (lookup column from table  GL_Accounts)	CostCenter (lookup column from table CC)	Year	January	February	March
73608900	40001137	2024	500	1000	1500
73608900	40001138	2024	200	600	900

[Chris-D]

Hi @MegaOctane1, you can optimise this by caching some of the data in advance. It looks like your lookup tables CC and GL_Accounts aren't going to change much so we can load these on page visible to make them available when needed. Use Concurrent() to async the requests.

On page visible, look up this information so it's available instantly when needed:

// In your Screen.OnVisible property:
Concurrent(
    UpdateContext({ CCLookUp: Filter( CC, Ansvarlig.Email = varUser.myProfile.mail)}),
    UpdateContext({ GLAccountLookUp: Filter( 'GL Accounts', GLType = "Other Cost")})
);

So your filter should hopefully look like this:

With({
    MyDisplayList: Filter(
        Budsjett,
        Year = varYear,
        CostCenter.Value in (CCLookUp).CostCenter,
        IsBlank(FilterCC_2.Selected.Value) || CostCenter.Value = FilterCC_2.Selected.Value,
        GL_Account.Value in (GLAccountLookUp).GLAccount,
        varReset
    )
},
    Sort(
        MyDisplayList,
        Endret,
        SortOrder.Descending
    )
)

Can you do this first and check it's working? I fiddled with your formatting a little too. If this is ok I can try and fix your delegation warning for you. 

EDIT:

Looking at this a bit closer, I'm confused by the line:

IsBlank(FilterCC_2.Selected.Value) || CostCenter.Value = FilterCC_2.Selected.Value,

I'm not sure this should work. Let me know your results when you've had a chance to test my code. 

[MegaOctane1]

Hi,
It works. I still have a delegation warning. 
Also for some cost centers, I get a black screen, and for other, I do not... Not sure what is triggering this.
The meaning of this line is to show me all Cost centers, or show the cost center i have selected in combobox, or select all if nothing is selected in combo box.

IsBlank(FilterCC_2.Selected.Value) || CostCenter.Value = FilterCC_2.Selected.Value,

With({
    MyDisplayList: Filter(
        Budsjett;
        Year = varYear;
        CostCenter.Value in (CCLookUp).CostCenter;
        IsBlank(FilterCC.Selected.Value) || CostCenter.Value = FilterCC.Selected.Value;
        GL_Account.Value in (GLAccountLookUp).GLAccount;varReset
    )
};
    Sort(
        MyDisplayList;
        Endret;
        SortOrder.Descending
    )
)

But for other Cost centers:

[Chris-D · Accepted solution]

Yeah that syntax won't work how you think it is working. If you put that into a text label you'll see it's output is simply true/false. 

To do dropdown lookups with defaults you have to do an extra filter stage. You also can't use Blank and 'in' with Filters on a data source without getting the delegation warning, but, you can filter a collection fine. So the way to get round this is to use a few With()'s to break it into stages. 

Try this:

With({
    MyDisplayList: 

    With({
        // First, get some data that won't cause a delegation warning
        // I'll add varReset here because it looks like you're using that for every query
        Data: Filter( Budsjett, Year = varYear, varReset)
    },
        With({
            // Now we have a collection to work with we don't get delegation warnings
            // We can filter the 'in' operators here and overwrite the Data variable
            Data: Filter( 
                Data, 
                CostCenter.Value in (CCLookUp).CostCenter,
                GL_Account.Value in (GLAccountLookUp).GLAccount
            )
        },
            // Now check the combo box
            If( !IsBlank(FilterCC.Selected.Value),
                // If it's not blank, filter the data again
                Filter( Data, CostCenter.Value in (FilterCC.SelectedItems).Value),

                // If it is blank, just return the data from the previous step
                Data
            )
        )
    )
},
    Sort(
        MyDisplayList,
        Endret,
        SortOrder.Descending
    )
)

[MegaOctane1]

Thanks @Chris-D 
This worked.  the only issue left is the black screen. Not sure why it happens when I filter on certain Cost Centers. If i show all, then no problem. It works for approx. 1/3 of the cost centers, and gives me black screen for the rest. not sure whats wrong in Power Apps...

Also - i see you use "Updatecontext". is this better than creating a collection for CClookup and GLAccountLookup at "on start"?

[Chris-D]

Glad it's working. Bear in mind that getting around the delegation warning does not remove the delegation limit. 

I come from a development background where pollution of namespace is a thing. UpdateContext() creates a variable scoped only to the current screen whereas a collection is visible through the whole app which is why I prefer it. You can do filters and lookups on it, but you can't collect to it, which makes it perfect for storing lookup tables in the local scope. 

This is my opinion only, I don't think there's a right/wrong way, you could use a collection and it would work fine. 

Regarding your black screen, I'm not sure why this would be, but, for there to be a black screen there must be something black? You must have a black control somewhere which is becoming visible. Your screenshots look like the gallery contents are changing width somehow? 

[MegaOctane1]

It might be. I will try making the gallery width fixed. And see if that helps.