Power FX Filter Dataverse Table - Self

(1) Share

Report

Posted on by ay26274

Hi,

I have a requirement to self filter dataverse table and pull the Assigned rows which does not have associated Submitted rows present in same dataverse table. Below is sort of what I want however this clearly does not work and I am here seeking for possible solution ? Similar to SQL Subquery

SQL


Select * from  [dbo].[stages] where stagename = 'Assigned' and uniqueid not in (
Select distinct uniqueid
 from [dbo].[stages] where stagename = 'Submitted'
  )

POWER FX Desired but does NOT work


Filter(
    Stages,
    stage = 'Stage (Stages)'.Assigned &&
    !(UniqueID in ShowColumns(
        Filter(
            Stages,
            stage = 'Stage (Stages)'.Submitted
        ),
        UniqueID
    ))
)

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Michael E. Gernaey 53,433 Super User 2025 Season 2 on at

Like (0)

Report
Hi,

I re-wrote it for you as you have it in SQL put in Power FX

Filter(Stages, stage = 'Stage (Stages)'.Assigned And Not(UniqueID in Distinct(Filter(Stages, stage = 'Stage (Stages)'.Submitted),UniqueID)) )

Was this reply helpful? Yes No
ay26274 16 on at

Like (0)

Report
Filter(Stages, stage = 'Stage (Stages)'.Assigned And Not(UniqueID in Distinct(Filter(Stages, stage = 'Stage (Stages)'.Submitted),UniqueID)) )

This gives me two delegation warnings and the Nested Filter says it has invalid argument.

I went with creating two collections and filter each other which works.

It could have been great if I could do it directly on Dataverse through Power FX instead of Collections route which is more burden on the app.

Was this reply helpful? Yes No
Michael E. Gernaey 53,433 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

Please share what you got when you wrote mine, it was late.

The Delegation warnings are easy enough to get rid of, and your problem isn't hard to solve :-) But we answered based on the little info we have (no access to see your lists, tables, names, etc) so we have to guess.

Let me know. It's better to learn to do it the right way one time :-).

Was this reply helpful? Yes No