PowerApps - Issue with filter combined with AddCollumns

[Wilder1626]

Hi

I'm trying to add a filter to an existing AddColumns code. The problem i have is that it does not give any error, but it just don't give me any result.

Filter(
    AddColumns(
        '[QA].[SHPG_LOC_T]',
        "PST_CD",
        LookUp(
            '[QA].[ADDR_T]',
            ADDR_ID = '[QA].[SHPG_LOC_T]'[@ADDR_ID]
        )
    ),
    SHPG_LOC_CD = "9568562"
)

If i just add columns it works. (over 9000 records)

AddColumns(
        '[QA].[SHPG_LOC_T]',
        "PST_CD",
        LookUp(
            '[QA].[ADDR_T]',
            ADDR_ID = '[QA].[SHPG_LOC_T]'[@ADDR_ID]
        )
    )

If i just filter the Ship_loc_cd, it also work. (1 single record)

Filter(
        '[QA].[SHPG_LOC_T]',
    SHPG_LOC_CD = "9568562"
)

But when combined, no records and no error.

Am I doing this incorrectly?

[WarrenBelz]

Hi @Wilder1626 ,

Firstly, you have two non-Delegable Elements there - the output numbers of AddColumns has to be within your Delegation limit and a Relational Lookup to another table is also non-Delegable.

If a Delegable filter produces under your (500-2000) limit, then AddColumns will function as expected.

Assuming the table '[QA].[ADDR_T]' has less than 2,000 records (or the newest 2,000 will do the job and you have a sequential numeric identifier), you can do this 

With(
   {
      wADDR:
      Sort(
         '[QA].[ADDR_T]',
         YourIdentifier,
         Descending
      )
   },
   Filter(
      AddColumns(
         '[QA].[SHPG_LOC_T]',
         "PST_CD",
         LookUp(
            wADDR,
            ADDR_ID = '[JDATM_PROD].[SHPG_LOC_T]'[@ADDR_ID]
         )
      ),
      SHPG_LOC_CD = "9568562"
   )
)

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.

[Wilder1626]

Hi WarrenBelz
It's really weird. I just tried the above without any errors what so ever, but zero results still.
But what i can tell you is that ADD_T and SHPG_LOC_T tables has 9000 records each.
So am i right to say that if no error and if no data showing, that's because I'm over the limite of 500-2000?

[WarrenBelz]

@Wilder1626 ,

Yes - I cannot see your data, but that would be a good assumption. Either that or your Filter/Lookup logic is flawed.

I have a blog on Delegation with some work around suggestions that may assist.

The thing you quickly learn with Power Apps is to plan your data structure before you do the app with the Delegation limitations in mind - I have a blog on this as well. 

Sometimes (this is where the pragmatist rather than purist comes in), I need to de-normalize my data and I have perfectly Delegable queries on lists of 40,000 and more.

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.

[Wilder1626]

I will start reading your blog. I'm seeing so many new good things, while others are not easy to understand for now. This will help me in my recent PowerApps self-learning journey. Thanks again.